{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 正式赛题——船运到达时间预测\n",
    "在企业全球化业务体系中，海运物流作为其最重要的一项支撑。其中，船运公司会和数据供应公司进行合作，对运输用的船通过GPS进行定位以监控船的位置；在运输管理的过程中，货物到达目的港的时间是非常重要的一项数据，那么需要通过船运的历史数据构建模型，对目的港到达时间进行预测，预测时间简称为ETA（estimated time of arrival），目的港到达时间预测为ARRIVAL_ETA。\n",
    "本次大赛提供历史运单GPS数据、历史运单事件数据、港口坐标数据，**预测货物运单的到达时间，对应“历史运单事件”数据中EVENT_CODE字段值为ARRIVAL AT PORT时EVENT_CONVOLUTION_DATE的时间值**。\n",
    "\n",
    "一、比赛数据\n",
    "大赛提供脱敏后的训练数据及测试数据，训练数据集包括：历史运单GPS数据、历史运单事件数据、港口坐标数据，这些数据主要用于参赛队伍训练模型，制定预估策略；测试运单数据为不同运单、运输过程中的不同位置所构成，供选手测试对应的ETA时间。\n",
    "货物运单在船运过程中，会产生大量的GPS运单数据，记录为“历史运单GPS数据”；货物运单在船运过程中离开起运港、到达中转港、到达目的港等关键事件，记录为“历史运单事件数据”；“港口的坐标数据“为与运单船运相关的港口坐标信息。\n",
    "允许选手合理增加与题目相关的外部数据进行纠正，如大赛提供的港口坐标数据存在偏差时可自行补充数据纠正"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "from geopy.distance import geodesic"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "## 坐标数据描述每个运单在船运的过程中涉及的港口位置信息:\n",
    "- 港口名称\n",
    "- 港口的经度坐标\n",
    "- 港口的纬度坐标\n",
    "- 国家\n",
    "- 省|州\n",
    "- 城市\n",
    "- 县|区\n",
    "- 详细地址。\n",
    "- 港口编码，即港口的字母简码"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "#港口坐标数据\n",
    "#港口坐标数据描述每个运单在船运的过程中涉及的港口位置信息。\n",
    "port=pd.read_csv('./event_port/port.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>LONGITUDE</th>\n",
       "      <th>LATITUDE</th>\n",
       "      <th>PORT_CODE</th>\n",
       "      <th>TRANSPORT_NODE_ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>2456.000000</td>\n",
       "      <td>2456.000000</td>\n",
       "      <td>2.500000e+01</td>\n",
       "      <td>2.420000e+03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>16.518344</td>\n",
       "      <td>18.550666</td>\n",
       "      <td>3.458640e+07</td>\n",
       "      <td>5.477894e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>71.399595</td>\n",
       "      <td>22.590817</td>\n",
       "      <td>7.384261e+07</td>\n",
       "      <td>7.974048e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>-175.183605</td>\n",
       "      <td>-53.793502</td>\n",
       "      <td>2.462000e+06</td>\n",
       "      <td>1.170000e+05</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>-3.457919</td>\n",
       "      <td>0.611022</td>\n",
       "      <td>2.576000e+06</td>\n",
       "      <td>2.927750e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>7.676975</td>\n",
       "      <td>18.432586</td>\n",
       "      <td>3.519000e+06</td>\n",
       "      <td>3.553500e+06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>57.510925</td>\n",
       "      <td>37.748396</td>\n",
       "      <td>3.684000e+06</td>\n",
       "      <td>9.520150e+07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>178.557549</td>\n",
       "      <td>73.212735</td>\n",
       "      <td>2.118270e+08</td>\n",
       "      <td>2.842170e+08</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         LONGITUDE     LATITUDE     PORT_CODE  TRANSPORT_NODE_ID\n",
       "count  2456.000000  2456.000000  2.500000e+01       2.420000e+03\n",
       "mean     16.518344    18.550666  3.458640e+07       5.477894e+07\n",
       "std      71.399595    22.590817  7.384261e+07       7.974048e+07\n",
       "min    -175.183605   -53.793502  2.462000e+06       1.170000e+05\n",
       "25%      -3.457919     0.611022  2.576000e+06       2.927750e+06\n",
       "50%       7.676975    18.432586  3.519000e+06       3.553500e+06\n",
       "75%      57.510925    37.748396  3.684000e+06       9.520150e+07\n",
       "max     178.557549    73.212735  2.118270e+08       2.842170e+08"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "port.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###  历史运单事件数据. 历史运单事件数据描述每个运单在船运的过程中，与港口相关的关键信息，如离开起运港、到达目的港等\n",
    "- loadingOrder 运单号，与历史运单GPS数据中的loadingOrder字段一致\n",
    "- EVENT_CODE 事件编码，主要事件包括：\n",
    " - TRANSIT PORT ATD实际离开中转港\n",
    " - SHIPMENT ONBOARD DATE实际离开起运港\n",
    " - TRANSIT PORT ATA实际到达中转港\n",
    " - ARRIVAL AT PORT实际到达目的港\n",
    " - 注：部分船可能没有中转港\n",
    "- EVENT_LOCATION_ID 港口名称，对应“港口坐标数据”表中的字段TRANS_NODE_NAME\n",
    "- EVENT_CONVOLUTION_DATE  事件发生的时间，格式为：yyyy/MM/dd HH:mm:ss（dd与HH之间为两个空格）例如Event_code为“SHIPMENT ONBOARD DATE\"时，此字段表示船从起运港出发的时间。\n",
    "- EVENT_CODE为“ARRIVAL AT PORT\"时，此字段表示船到达目的港的时间。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 历史运单GPS数据\n",
    "#### 历史运单GPS数据描述每个运单在船运的过程中，所在船产生的GPS位置的相关信息。\n",
    "- loadingOrder 脱敏后的主运单，货物的运单编号，类似快递单号\n",
    "- carrierName 脱敏后的承运商名称，类似快递公司名称\n",
    "- timestamp 时间，格式为：yyyy-MM-dd'T'HH:mm:ss.SSSZ，如2019-09-05T16:33:17.000Z\n",
    "- longitude 货物在运输过程中，当前船舶所处的经度坐标，如114.234567\n",
    "- latitude 货物在运输过程中，当前船舶所处的纬度坐标，如21.234567\n",
    "- vesselMMSI 脱敏后的船舶海上移动业务识别码MMSI， 唯一标识，对应到每一艘船\n",
    "- speed 单位km/h，货物在运输过程中，当前船舶的瞬时速度，部分数据未提供的可自行计算。\n",
    "- direction 当前船舶的行驶方向，正北是0度，31480代表西北方向314.80度，900代表正北偏东9度。\n",
    "- vesselNextport 船舶将要到达的下一港口，港口名称可能不规范，如CNQIN、CN QIN、CN QINGDAO都代表下一站为中国青岛港口。\n",
    "- vesselNextportETA 船运公司给出的到“下一个港口”预计到达时间，格式为：yyyy-MM-dd'T'HH:mm:ss.SSSZ，如2019-09-12T16:33:17.000Z\n",
    "- vesselStatus 当前船舶航行状态，主要包括：\n",
    "  - moored\n",
    "  - under way using engine\n",
    "  - not under command\n",
    "  - at anchor\n",
    "  - under way sailing\n",
    "  - constrained by her draught\n",
    "- vesselDatasource 船舶数据来源（岸基/卫星）：Coastal AIS，Satellite\n",
    "- TRANSPORT_TRACE  船的路由，由“-”连接组成，例如CNSHK-MYPKG-MYTPP。由承运商预先录入，实际小概率存在不按此路由行驶（如遇塞港时），但最终会到达目的港口。\n",
    "\n",
    "### 注意!!: 一个运单对应一艘船，一艘船可以对应多个运单"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(151948653, 3)\n",
      "(25132, 3)\n"
     ]
    }
   ],
   "source": [
    "gpsdfx=pd.read_csv('train0711.csv',usecols=[0,1,5])\n",
    "gpsdfx.columns =['loadingOrder','carrierName', 'vesselMMSI']\n",
    "print(gpsdfx.shape)\n",
    "gpsdfx.drop_duplicates(inplace=True)\n",
    "gpsdfx=gpsdfx.reset_index(drop=True)\n",
    "print(gpsdfx.shape)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "gpsdfx['carrierName'].isna().sum(),gpsdfx['vesselMMSI'].isna().sum()\n",
    "gpsdfx.to_csv('carrierName_vesselMMSI.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "#清洗数据:删除没有trace的运单，有中间trace的运单以及重复的运单\n",
    "#读取所有行\n",
    "gpsdfx=pd.read_csv('train0711.csv',usecols=[0,2,3,4,6,7,12])\n",
    "gpsdfx.columns =['loadingOrder','timestamp', 'longitude','latitude','speed', 'direction','TRANSPORT_TRACE']\n",
    "gpsdfx.shape\n",
    "#将有空值的行删除\n",
    "gpsdfx.dropna(inplace=True)\n",
    "gpsdfx=gpsdfx.reset_index(drop=True)\n",
    "'''\n",
    "#删除有中间港口的运单\n",
    "traces=gpsdfx['TRANSPORT_TRACE'].values\n",
    "skips=[]\n",
    "for i in range(len(traces)):\n",
    "    if i%20000000==0:\n",
    "        print(i)\n",
    "    if len(traces[i].split('-'))>2:\n",
    "            skips.append(i)\n",
    "raw=[i for i in range(len(gpsdfx))]\n",
    "reserve=list(set(raw)-set(skips))\n",
    "clean_df=gpsdfx.loc[reserve]\n",
    "clean_df=clean_df.reset_index(drop=True)\n",
    "'''\n",
    "#删除重复行\n",
    "clean_df=gpsdfx\n",
    "re=clean_df.duplicated()\n",
    "re=list(re)\n",
    "skip_dup=[]\n",
    "for i in range(len(re)):\n",
    "    if re[i]==False:\n",
    "        skip_dup.append(i)\n",
    "clean_df=clean_df.loc[skip_dup]\n",
    "clean_df=clean_df.reset_index(drop=True)\n",
    "clean_df.to_csv('GPS_clean712v1.csv',index=False)#写入清洗后的数据，\n",
    "#对于trace数据，还需要进一步的清洗\n",
    "trace=clean_df['TRANSPORT_TRACE'].unique()\n",
    "reserve_trace=[]\n",
    "for i in trace:\n",
    "    if '-'in i:\n",
    "        reserve_trace.append(i)\n",
    "#\n",
    "reserve_index=[]\n",
    "df_trace=clean_df['TRANSPORT_TRACE'].values\n",
    "for index in range(len(df_trace)):\n",
    "    if df_trace[index] in reserve_trace:\n",
    "        reserve_index.append(index)\n",
    "clean_df=clean_df.loc[reserve_index].reset_index(drop=True)\n",
    "clean_df.to_csv('clean_dataset/GPS_clean712v2.csv',index=False)#写入清洗后的数据大约3.7kw条数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Administrator\\AppData\\Local\\Continuum\\anaconda3\\envs\\huawei\\lib\\site-packages\\ipykernel_launcher.py:47: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "C:\\Users\\Administrator\\AppData\\Local\\Continuum\\anaconda3\\envs\\huawei\\lib\\site-packages\\ipykernel_launcher.py:48: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "C:\\Users\\Administrator\\AppData\\Local\\Continuum\\anaconda3\\envs\\huawei\\lib\\site-packages\\ipykernel_launcher.py:49: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "C:\\Users\\Administrator\\AppData\\Local\\Continuum\\anaconda3\\envs\\huawei\\lib\\site-packages\\ipykernel_launcher.py:50: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n"
     ]
    }
   ],
   "source": [
    "#进一步和port数据进行关联\n",
    "clean_df=pd.read_csv('clean_dataset/GPS_clean712v2.csv')\n",
    "clean_df.sort_values(['loadingOrder', 'timestamp'], inplace = True)\n",
    "clean_df=clean_df.reset_index(drop=True)\n",
    "clean_df\n",
    "cut=[]\n",
    "temp_dic=[]\n",
    "portnames=port['TRANS_NODE_NAME'].values\n",
    "grouped=clean_df.groupby('loadingOrder')\n",
    "for name,group in grouped:\n",
    "    port_name=group['TRANSPORT_TRACE'][:1].reset_index(drop=True)[0]\n",
    "    s_e=port_name.split('-')\n",
    "    if s_e[0] in portnames and s_e[-1] in portnames:\n",
    "        continue\n",
    "    cut.append(name)\n",
    "clean_df=clean_df[~clean_df['loadingOrder'].isin(cut)]\n",
    "#\n",
    "def convert_name_xy(name):#输入港口名称\n",
    "    port_name=port[port['TRANS_NODE_NAME'].isin([name])].reset_index()\n",
    "    return port_name['LONGITUDE'][0],port_name['LATITUDE'][0]#返回港口经纬度\n",
    "#  \n",
    "start_x=[]#起点\n",
    "start_y=[]#起点\n",
    "end_x=[]#终点\n",
    "end_y=[]#终点\n",
    "#存储中间结果,避免重复计算\n",
    "temp_dic={}\n",
    "values=clean_df['TRANSPORT_TRACE'].values\n",
    "for value in values:\n",
    "    s_e=value.split('-')\n",
    "    start_port=s_e[0]\n",
    "    end_port=s_e[-1]\n",
    "    if start_port in temp_dic:\n",
    "        re=temp_dic[start_port]\n",
    "    else:\n",
    "        re=convert_name_xy(value.split('-')[0])\n",
    "        temp_dic[start_port]=re\n",
    "    start_x.append(re[0])\n",
    "    start_y.append(re[1])\n",
    "    if end_port in temp_dic:\n",
    "        re=temp_dic[end_port]\n",
    "    else:\n",
    "        re=convert_name_xy(value.split('-')[-1])\n",
    "        temp_dic[end_port]=re\n",
    "    end_x.append(re[0])\n",
    "    end_y.append(re[1])\n",
    "clean_df['start_x']=start_x\n",
    "clean_df['start_y']=start_y\n",
    "clean_df['end_x']=end_x\n",
    "clean_df['end_y']=end_y\n",
    "#clean_df.to_csv('clean_dataset/GPS_clean625v1.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "clean_df.to_csv('clean_dataset/dataHasXY.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2.3920541380094607"
      ]
     },
     "execution_count": 63,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "geodesic((3.013783\t,101.355750\t),(3.034709,101.361204)).km"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>loadingOrder</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>longitude</th>\n",
       "      <th>latitude</th>\n",
       "      <th>speed</th>\n",
       "      <th>direction</th>\n",
       "      <th>TRANSPORT_TRACE</th>\n",
       "      <th>start_x</th>\n",
       "      <th>start_y</th>\n",
       "      <th>end_x</th>\n",
       "      <th>end_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AA191175561416</td>\n",
       "      <td>2019-01-28T16:12:59.000Z</td>\n",
       "      <td>114.260392</td>\n",
       "      <td>22.571047</td>\n",
       "      <td>0</td>\n",
       "      <td>12670</td>\n",
       "      <td>CNYTN-MXZLO</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-104.305571</td>\n",
       "      <td>19.085961</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AA191175561416</td>\n",
       "      <td>2019-01-28T16:22:38.000Z</td>\n",
       "      <td>114.260438</td>\n",
       "      <td>22.571125</td>\n",
       "      <td>0</td>\n",
       "      <td>14790</td>\n",
       "      <td>CNYTN-MXZLO</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-104.305571</td>\n",
       "      <td>19.085961</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AA191175561416</td>\n",
       "      <td>2019-01-28T16:30:55.000Z</td>\n",
       "      <td>114.260693</td>\n",
       "      <td>22.571567</td>\n",
       "      <td>0</td>\n",
       "      <td>21510</td>\n",
       "      <td>CNYTN-MXZLO</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-104.305571</td>\n",
       "      <td>19.085961</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AA191175561416</td>\n",
       "      <td>2019-01-28T16:37:35.000Z</td>\n",
       "      <td>114.260392</td>\n",
       "      <td>22.571463</td>\n",
       "      <td>0</td>\n",
       "      <td>19900</td>\n",
       "      <td>CNYTN-MXZLO</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-104.305571</td>\n",
       "      <td>19.085961</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AA191175561416</td>\n",
       "      <td>2019-01-28T16:45:56.000Z</td>\n",
       "      <td>114.260647</td>\n",
       "      <td>22.571510</td>\n",
       "      <td>0</td>\n",
       "      <td>21360</td>\n",
       "      <td>CNYTN-MXZLO</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-104.305571</td>\n",
       "      <td>19.085961</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5193</th>\n",
       "      <td>AA191175561416</td>\n",
       "      <td>2019-02-23T15:51:55.000Z</td>\n",
       "      <td>-104.778320</td>\n",
       "      <td>19.093467</td>\n",
       "      <td>36</td>\n",
       "      <td>10690</td>\n",
       "      <td>CNYTN-MXZLO</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-104.305571</td>\n",
       "      <td>19.085961</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5194</th>\n",
       "      <td>AA191175561416</td>\n",
       "      <td>2019-02-23T16:52:48.000Z</td>\n",
       "      <td>-104.444422</td>\n",
       "      <td>19.017923</td>\n",
       "      <td>27</td>\n",
       "      <td>10870</td>\n",
       "      <td>CNYTN-MXZLO</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-104.305571</td>\n",
       "      <td>19.085961</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5195</th>\n",
       "      <td>AA191175561416</td>\n",
       "      <td>2019-02-23T18:05:25.000Z</td>\n",
       "      <td>-104.316002</td>\n",
       "      <td>19.063652</td>\n",
       "      <td>11</td>\n",
       "      <td>11930</td>\n",
       "      <td>CNYTN-MXZLO</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-104.305571</td>\n",
       "      <td>19.085961</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5196</th>\n",
       "      <td>AA191175561416</td>\n",
       "      <td>2019-02-23T18:32:15.000Z</td>\n",
       "      <td>-104.299600</td>\n",
       "      <td>19.059592</td>\n",
       "      <td>0</td>\n",
       "      <td>24100</td>\n",
       "      <td>CNYTN-MXZLO</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-104.305571</td>\n",
       "      <td>19.085961</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5197</th>\n",
       "      <td>AA191175561416</td>\n",
       "      <td>2019-02-23T18:39:35.000Z</td>\n",
       "      <td>-104.299630</td>\n",
       "      <td>19.059593</td>\n",
       "      <td>0</td>\n",
       "      <td>22540</td>\n",
       "      <td>CNYTN-MXZLO</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-104.305571</td>\n",
       "      <td>19.085961</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5198 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        loadingOrder                 timestamp   longitude   latitude  speed  \\\n",
       "0     AA191175561416  2019-01-28T16:12:59.000Z  114.260392  22.571047      0   \n",
       "1     AA191175561416  2019-01-28T16:22:38.000Z  114.260438  22.571125      0   \n",
       "2     AA191175561416  2019-01-28T16:30:55.000Z  114.260693  22.571567      0   \n",
       "3     AA191175561416  2019-01-28T16:37:35.000Z  114.260392  22.571463      0   \n",
       "4     AA191175561416  2019-01-28T16:45:56.000Z  114.260647  22.571510      0   \n",
       "...              ...                       ...         ...        ...    ...   \n",
       "5193  AA191175561416  2019-02-23T15:51:55.000Z -104.778320  19.093467     36   \n",
       "5194  AA191175561416  2019-02-23T16:52:48.000Z -104.444422  19.017923     27   \n",
       "5195  AA191175561416  2019-02-23T18:05:25.000Z -104.316002  19.063652     11   \n",
       "5196  AA191175561416  2019-02-23T18:32:15.000Z -104.299600  19.059592      0   \n",
       "5197  AA191175561416  2019-02-23T18:39:35.000Z -104.299630  19.059593      0   \n",
       "\n",
       "      direction TRANSPORT_TRACE     start_x  start_y       end_x      end_y  \n",
       "0         12670     CNYTN-MXZLO  114.275347  22.5777 -104.305571  19.085961  \n",
       "1         14790     CNYTN-MXZLO  114.275347  22.5777 -104.305571  19.085961  \n",
       "2         21510     CNYTN-MXZLO  114.275347  22.5777 -104.305571  19.085961  \n",
       "3         19900     CNYTN-MXZLO  114.275347  22.5777 -104.305571  19.085961  \n",
       "4         21360     CNYTN-MXZLO  114.275347  22.5777 -104.305571  19.085961  \n",
       "...         ...             ...         ...      ...         ...        ...  \n",
       "5193      10690     CNYTN-MXZLO  114.275347  22.5777 -104.305571  19.085961  \n",
       "5194      10870     CNYTN-MXZLO  114.275347  22.5777 -104.305571  19.085961  \n",
       "5195      11930     CNYTN-MXZLO  114.275347  22.5777 -104.305571  19.085961  \n",
       "5196      24100     CNYTN-MXZLO  114.275347  22.5777 -104.305571  19.085961  \n",
       "5197      22540     CNYTN-MXZLO  114.275347  22.5777 -104.305571  19.085961  \n",
       "\n",
       "[5198 rows x 11 columns]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "clean_df[clean_df['loadingOrder'].isin(['AA191175561416'])]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>TRANS_NODE_NAME</th>\n",
       "      <th>LONGITUDE</th>\n",
       "      <th>LATITUDE</th>\n",
       "      <th>COUNTRY</th>\n",
       "      <th>STATE</th>\n",
       "      <th>CITY</th>\n",
       "      <th>REGION</th>\n",
       "      <th>ADDRESS</th>\n",
       "      <th>PORT_CODE</th>\n",
       "      <th>TRANSPORT_NODE_ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>686</th>\n",
       "      <td>ZADUR</td>\n",
       "      <td>31.05008</td>\n",
       "      <td>-29.868304</td>\n",
       "      <td>South Africa</td>\n",
       "      <td>KwaZulu-Natal</td>\n",
       "      <td>Durban</td>\n",
       "      <td>Point</td>\n",
       "      <td>79 Browns Rd, Point, Durban, 4001, South Africa</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2996000.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    TRANS_NODE_NAME  LONGITUDE   LATITUDE       COUNTRY          STATE  \\\n",
       "686           ZADUR   31.05008 -29.868304  South Africa  KwaZulu-Natal   \n",
       "\n",
       "       CITY REGION                                          ADDRESS  \\\n",
       "686  Durban  Point  79 Browns Rd, Point, Durban, 4001, South Africa   \n",
       "\n",
       "     PORT_CODE  TRANSPORT_NODE_ID  \n",
       "686        NaN          2996000.0  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "port[port['TRANS_NODE_NAME'].isin(['ZADUR'])]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 总共12171个运单是可以找到起点和终点坐标的"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "12171"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(clean_df['loadingOrder'].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 78,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(195378, 5318)"
      ]
     },
     "execution_count": 78,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def cut_zero_end(indexs,data):\n",
    "    for i in range(len(data)-1,0,-1):\n",
    "        if data[i]!=0:\n",
    "            break\n",
    "    return indexs[i+1:len(data)]\n",
    "def cut_zero_start(indexs,data):\n",
    "    for i in range(len(data)):\n",
    "        if data[i]!=0:\n",
    "            break\n",
    "    return indexs[:i]\n",
    "cut_list=[]\n",
    "cut_name=[]\n",
    "thres=200\n",
    "grouped=clean_df.groupby('loadingOrder')\n",
    "for name,group in grouped:\n",
    "    if len(group)<thres:#记录小于thres条的去掉\n",
    "        cut_list+=list(group.index)\n",
    "        continue\n",
    "    data=list(group['speed'][-thres:])\n",
    "    indexs=list(group['speed'][-thres:].index)\n",
    "    if data[-1]!=0 or data[1]!=0:\n",
    "        cut_name.append(name)\n",
    "    if data[-1]==0:\n",
    "        cut_list+=cut_zero_end(indexs,data)\n",
    "    data=list(group['speed'][:thres])\n",
    "    indexs=list(group['speed'][:thres].index)\n",
    "    if data[0]==0:\n",
    "        cut_list+=cut_zero_start(indexs,data)\n",
    "    \n",
    "len(cut_list),len(cut_name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 82,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>loadingOrder</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>longitude</th>\n",
       "      <th>latitude</th>\n",
       "      <th>speed</th>\n",
       "      <th>direction</th>\n",
       "      <th>TRANSPORT_TRACE</th>\n",
       "      <th>start_x</th>\n",
       "      <th>start_y</th>\n",
       "      <th>end_x</th>\n",
       "      <th>end_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AA191175561416</td>\n",
       "      <td>2019-01-29T14:47:31.000Z</td>\n",
       "      <td>114.260940</td>\n",
       "      <td>22.570957</td>\n",
       "      <td>1</td>\n",
       "      <td>13340</td>\n",
       "      <td>CNYTN-MXZLO</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.577700</td>\n",
       "      <td>-104.305571</td>\n",
       "      <td>19.085961</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AA191175561416</td>\n",
       "      <td>2019-01-29T14:53:23.000Z</td>\n",
       "      <td>114.261352</td>\n",
       "      <td>22.570832</td>\n",
       "      <td>2</td>\n",
       "      <td>8420</td>\n",
       "      <td>CNYTN-MXZLO</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.577700</td>\n",
       "      <td>-104.305571</td>\n",
       "      <td>19.085961</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AA191175561416</td>\n",
       "      <td>2019-01-29T14:57:35.000Z</td>\n",
       "      <td>114.263375</td>\n",
       "      <td>22.570643</td>\n",
       "      <td>4</td>\n",
       "      <td>11890</td>\n",
       "      <td>CNYTN-MXZLO</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.577700</td>\n",
       "      <td>-104.305571</td>\n",
       "      <td>19.085961</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AA191175561416</td>\n",
       "      <td>2019-01-29T15:00:10.000Z</td>\n",
       "      <td>114.265763</td>\n",
       "      <td>22.568548</td>\n",
       "      <td>3</td>\n",
       "      <td>14650</td>\n",
       "      <td>CNYTN-MXZLO</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.577700</td>\n",
       "      <td>-104.305571</td>\n",
       "      <td>19.085961</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AA191175561416</td>\n",
       "      <td>2019-01-29T15:03:50.000Z</td>\n",
       "      <td>114.267432</td>\n",
       "      <td>22.566027</td>\n",
       "      <td>7</td>\n",
       "      <td>13280</td>\n",
       "      <td>CNYTN-MXZLO</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.577700</td>\n",
       "      <td>-104.305571</td>\n",
       "      <td>19.085961</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35655754</th>\n",
       "      <td>ZZ907366774129</td>\n",
       "      <td>2020-02-24T14:29:01.000Z</td>\n",
       "      <td>101.352083</td>\n",
       "      <td>3.012183</td>\n",
       "      <td>1</td>\n",
       "      <td>4700</td>\n",
       "      <td>CNSHK-MYPKG</td>\n",
       "      <td>113.863058</td>\n",
       "      <td>22.559462</td>\n",
       "      <td>101.361204</td>\n",
       "      <td>3.034709</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35655755</th>\n",
       "      <td>ZZ907366774129</td>\n",
       "      <td>2020-02-24T14:30:47.000Z</td>\n",
       "      <td>101.352667</td>\n",
       "      <td>3.012750</td>\n",
       "      <td>1</td>\n",
       "      <td>4500</td>\n",
       "      <td>CNSHK-MYPKG</td>\n",
       "      <td>113.863058</td>\n",
       "      <td>22.559462</td>\n",
       "      <td>101.361204</td>\n",
       "      <td>3.034709</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35655756</th>\n",
       "      <td>ZZ907366774129</td>\n",
       "      <td>2020-02-24T14:36:41.000Z</td>\n",
       "      <td>101.354883</td>\n",
       "      <td>3.013717</td>\n",
       "      <td>1</td>\n",
       "      <td>9900</td>\n",
       "      <td>CNSHK-MYPKG</td>\n",
       "      <td>113.863058</td>\n",
       "      <td>22.559462</td>\n",
       "      <td>101.361204</td>\n",
       "      <td>3.034709</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35655757</th>\n",
       "      <td>ZZ907366774129</td>\n",
       "      <td>2020-02-24T14:38:30.000Z</td>\n",
       "      <td>101.355333</td>\n",
       "      <td>3.013700</td>\n",
       "      <td>1</td>\n",
       "      <td>8500</td>\n",
       "      <td>CNSHK-MYPKG</td>\n",
       "      <td>113.863058</td>\n",
       "      <td>22.559462</td>\n",
       "      <td>101.361204</td>\n",
       "      <td>3.034709</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35655758</th>\n",
       "      <td>ZZ907366774129</td>\n",
       "      <td>2020-02-24T14:40:50.000Z</td>\n",
       "      <td>101.355750</td>\n",
       "      <td>3.013783</td>\n",
       "      <td>1</td>\n",
       "      <td>7000</td>\n",
       "      <td>CNSHK-MYPKG</td>\n",
       "      <td>113.863058</td>\n",
       "      <td>22.559462</td>\n",
       "      <td>101.361204</td>\n",
       "      <td>3.034709</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>35655759 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            loadingOrder                 timestamp   longitude   latitude  \\\n",
       "0         AA191175561416  2019-01-29T14:47:31.000Z  114.260940  22.570957   \n",
       "1         AA191175561416  2019-01-29T14:53:23.000Z  114.261352  22.570832   \n",
       "2         AA191175561416  2019-01-29T14:57:35.000Z  114.263375  22.570643   \n",
       "3         AA191175561416  2019-01-29T15:00:10.000Z  114.265763  22.568548   \n",
       "4         AA191175561416  2019-01-29T15:03:50.000Z  114.267432  22.566027   \n",
       "...                  ...                       ...         ...        ...   \n",
       "35655754  ZZ907366774129  2020-02-24T14:29:01.000Z  101.352083   3.012183   \n",
       "35655755  ZZ907366774129  2020-02-24T14:30:47.000Z  101.352667   3.012750   \n",
       "35655756  ZZ907366774129  2020-02-24T14:36:41.000Z  101.354883   3.013717   \n",
       "35655757  ZZ907366774129  2020-02-24T14:38:30.000Z  101.355333   3.013700   \n",
       "35655758  ZZ907366774129  2020-02-24T14:40:50.000Z  101.355750   3.013783   \n",
       "\n",
       "          speed  direction TRANSPORT_TRACE     start_x    start_y       end_x  \\\n",
       "0             1      13340     CNYTN-MXZLO  114.275347  22.577700 -104.305571   \n",
       "1             2       8420     CNYTN-MXZLO  114.275347  22.577700 -104.305571   \n",
       "2             4      11890     CNYTN-MXZLO  114.275347  22.577700 -104.305571   \n",
       "3             3      14650     CNYTN-MXZLO  114.275347  22.577700 -104.305571   \n",
       "4             7      13280     CNYTN-MXZLO  114.275347  22.577700 -104.305571   \n",
       "...         ...        ...             ...         ...        ...         ...   \n",
       "35655754      1       4700     CNSHK-MYPKG  113.863058  22.559462  101.361204   \n",
       "35655755      1       4500     CNSHK-MYPKG  113.863058  22.559462  101.361204   \n",
       "35655756      1       9900     CNSHK-MYPKG  113.863058  22.559462  101.361204   \n",
       "35655757      1       8500     CNSHK-MYPKG  113.863058  22.559462  101.361204   \n",
       "35655758      1       7000     CNSHK-MYPKG  113.863058  22.559462  101.361204   \n",
       "\n",
       "              end_y  \n",
       "0         19.085961  \n",
       "1         19.085961  \n",
       "2         19.085961  \n",
       "3         19.085961  \n",
       "4         19.085961  \n",
       "...             ...  \n",
       "35655754   3.034709  \n",
       "35655755   3.034709  \n",
       "35655756   3.034709  \n",
       "35655757   3.034709  \n",
       "35655758   3.034709  \n",
       "\n",
       "[35655759 rows x 11 columns]"
      ]
     },
     "execution_count": 82,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gpsdf=clean_df.drop(index=cut_list)\n",
    "gpsdf=gpsdf.reset_index(drop=True)\n",
    "gpsdf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>loadingOrder</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>longitude</th>\n",
       "      <th>latitude</th>\n",
       "      <th>speed</th>\n",
       "      <th>direction</th>\n",
       "      <th>TRANSPORT_TRACE</th>\n",
       "      <th>start_x</th>\n",
       "      <th>start_y</th>\n",
       "      <th>end_x</th>\n",
       "      <th>end_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AB614679152621</td>\n",
       "      <td>2019-12-16T17:49:51.000Z</td>\n",
       "      <td>114.283133</td>\n",
       "      <td>22.578533</td>\n",
       "      <td>3</td>\n",
       "      <td>7940</td>\n",
       "      <td>CNYTN-ESVAL</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.577700</td>\n",
       "      <td>-0.327021</td>\n",
       "      <td>39.460366</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AB614679152621</td>\n",
       "      <td>2019-12-16T17:50:37.000Z</td>\n",
       "      <td>114.283450</td>\n",
       "      <td>22.578633</td>\n",
       "      <td>2</td>\n",
       "      <td>6620</td>\n",
       "      <td>CNYTN-ESVAL</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.577700</td>\n",
       "      <td>-0.327021</td>\n",
       "      <td>39.460366</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AB614679152621</td>\n",
       "      <td>2019-12-16T17:51:22.000Z</td>\n",
       "      <td>114.283700</td>\n",
       "      <td>22.578767</td>\n",
       "      <td>2</td>\n",
       "      <td>5900</td>\n",
       "      <td>CNYTN-ESVAL</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.577700</td>\n",
       "      <td>-0.327021</td>\n",
       "      <td>39.460366</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AB614679152621</td>\n",
       "      <td>2019-12-16T17:52:22.000Z</td>\n",
       "      <td>114.283917</td>\n",
       "      <td>22.578967</td>\n",
       "      <td>2</td>\n",
       "      <td>3830</td>\n",
       "      <td>CNYTN-ESVAL</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.577700</td>\n",
       "      <td>-0.327021</td>\n",
       "      <td>39.460366</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AB614679152621</td>\n",
       "      <td>2019-12-16T17:53:10.000Z</td>\n",
       "      <td>114.284017</td>\n",
       "      <td>22.579133</td>\n",
       "      <td>1</td>\n",
       "      <td>1680</td>\n",
       "      <td>CNYTN-ESVAL</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.577700</td>\n",
       "      <td>-0.327021</td>\n",
       "      <td>39.460366</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8165431</th>\n",
       "      <td>ZZ824778274922</td>\n",
       "      <td>2020-03-04T05:10:30.000Z</td>\n",
       "      <td>136.798000</td>\n",
       "      <td>35.024833</td>\n",
       "      <td>7</td>\n",
       "      <td>31590</td>\n",
       "      <td>CNSHK-JPNGO</td>\n",
       "      <td>113.863058</td>\n",
       "      <td>22.559462</td>\n",
       "      <td>136.847423</td>\n",
       "      <td>35.056571</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8165432</th>\n",
       "      <td>ZZ824778274922</td>\n",
       "      <td>2020-03-04T05:12:40.000Z</td>\n",
       "      <td>136.795667</td>\n",
       "      <td>35.026333</td>\n",
       "      <td>7</td>\n",
       "      <td>29530</td>\n",
       "      <td>CNSHK-JPNGO</td>\n",
       "      <td>113.863058</td>\n",
       "      <td>22.559462</td>\n",
       "      <td>136.847423</td>\n",
       "      <td>35.056571</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8165433</th>\n",
       "      <td>ZZ824778274922</td>\n",
       "      <td>2020-03-04T05:14:21.000Z</td>\n",
       "      <td>136.794000</td>\n",
       "      <td>35.026833</td>\n",
       "      <td>5</td>\n",
       "      <td>28670</td>\n",
       "      <td>CNSHK-JPNGO</td>\n",
       "      <td>113.863058</td>\n",
       "      <td>22.559462</td>\n",
       "      <td>136.847423</td>\n",
       "      <td>35.056571</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8165434</th>\n",
       "      <td>ZZ824778274922</td>\n",
       "      <td>2020-03-04T05:16:31.000Z</td>\n",
       "      <td>136.792333</td>\n",
       "      <td>35.027167</td>\n",
       "      <td>2</td>\n",
       "      <td>27440</td>\n",
       "      <td>CNSHK-JPNGO</td>\n",
       "      <td>113.863058</td>\n",
       "      <td>22.559462</td>\n",
       "      <td>136.847423</td>\n",
       "      <td>35.056571</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8165435</th>\n",
       "      <td>ZZ824778274922</td>\n",
       "      <td>2020-03-04T05:18:31.000Z</td>\n",
       "      <td>136.791667</td>\n",
       "      <td>35.027000</td>\n",
       "      <td>2</td>\n",
       "      <td>22580</td>\n",
       "      <td>CNSHK-JPNGO</td>\n",
       "      <td>113.863058</td>\n",
       "      <td>22.559462</td>\n",
       "      <td>136.847423</td>\n",
       "      <td>35.056571</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>8165436 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           loadingOrder                 timestamp   longitude   latitude  \\\n",
       "0        AB614679152621  2019-12-16T17:49:51.000Z  114.283133  22.578533   \n",
       "1        AB614679152621  2019-12-16T17:50:37.000Z  114.283450  22.578633   \n",
       "2        AB614679152621  2019-12-16T17:51:22.000Z  114.283700  22.578767   \n",
       "3        AB614679152621  2019-12-16T17:52:22.000Z  114.283917  22.578967   \n",
       "4        AB614679152621  2019-12-16T17:53:10.000Z  114.284017  22.579133   \n",
       "...                 ...                       ...         ...        ...   \n",
       "8165431  ZZ824778274922  2020-03-04T05:10:30.000Z  136.798000  35.024833   \n",
       "8165432  ZZ824778274922  2020-03-04T05:12:40.000Z  136.795667  35.026333   \n",
       "8165433  ZZ824778274922  2020-03-04T05:14:21.000Z  136.794000  35.026833   \n",
       "8165434  ZZ824778274922  2020-03-04T05:16:31.000Z  136.792333  35.027167   \n",
       "8165435  ZZ824778274922  2020-03-04T05:18:31.000Z  136.791667  35.027000   \n",
       "\n",
       "         speed  direction TRANSPORT_TRACE     start_x    start_y       end_x  \\\n",
       "0            3       7940     CNYTN-ESVAL  114.275347  22.577700   -0.327021   \n",
       "1            2       6620     CNYTN-ESVAL  114.275347  22.577700   -0.327021   \n",
       "2            2       5900     CNYTN-ESVAL  114.275347  22.577700   -0.327021   \n",
       "3            2       3830     CNYTN-ESVAL  114.275347  22.577700   -0.327021   \n",
       "4            1       1680     CNYTN-ESVAL  114.275347  22.577700   -0.327021   \n",
       "...        ...        ...             ...         ...        ...         ...   \n",
       "8165431      7      31590     CNSHK-JPNGO  113.863058  22.559462  136.847423   \n",
       "8165432      7      29530     CNSHK-JPNGO  113.863058  22.559462  136.847423   \n",
       "8165433      5      28670     CNSHK-JPNGO  113.863058  22.559462  136.847423   \n",
       "8165434      2      27440     CNSHK-JPNGO  113.863058  22.559462  136.847423   \n",
       "8165435      2      22580     CNSHK-JPNGO  113.863058  22.559462  136.847423   \n",
       "\n",
       "             end_y  \n",
       "0        39.460366  \n",
       "1        39.460366  \n",
       "2        39.460366  \n",
       "3        39.460366  \n",
       "4        39.460366  \n",
       "...            ...  \n",
       "8165431  35.056571  \n",
       "8165432  35.056571  \n",
       "8165433  35.056571  \n",
       "8165434  35.056571  \n",
       "8165435  35.056571  \n",
       "\n",
       "[8165436 rows x 11 columns]"
      ]
     },
     "execution_count": 89,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gpsdf"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [],
   "source": [
    "gpsdf=gpsdf[~gpsdf['loadingOrder'].isin(cut_name)].reset_index(drop=True)#还剩下1263条样本"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {},
   "outputs": [],
   "source": [
    "gpsdf.to_csv('clean_dataset/train1263.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "test_data_path = 'A_testData0531.csv'\n",
    "test_data=pd.read_csv(test_data_path)\n",
    "#test_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "test_trace=test_data['TRANSPORT_TRACE'].unique()\n",
    "train_trace=clean_df['TRANSPORT_TRACE'].unique()\n",
    "tmps=[]\n",
    "for i in test_trace:\n",
    "    if i in train_trace:\n",
    "        tmps.append(i)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "total=[]\n",
    "reserve=[]\n",
    "#grouped=clean_df.groupby('TRANSPORT_TRACE')\n",
    "value=clean_df['TRANSPORT_TRACE'].values\n",
    "for i in range(len(value)):\n",
    "    if value[i] in tmps:\n",
    "        reserve.append(i)\n",
    "xx=clean_df.loc[reserve].reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "4929.72202166065"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#\n",
    "#xx.to_csv('clean_dataset/clean17Trace.csv',index=False)\n",
    "4096599 /831"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "test_data_path = 'A_testData0531.csv'\n",
    "test_data=pd.read_csv(test_data_path)\n",
    "#test_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "test_trace=test_data['TRANSPORT_TRACE'].unique()\n",
    "train_trace=clean_df['TRANSPORT_TRACE'].unique()\n",
    "tmps=[]\n",
    "for i in test_trace:\n",
    "    if i in train_trace:\n",
    "        tmps.append(i)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## GPS中共1038(出发-目的地对)，没有中转港的共365条，有598条含有中转港的数据，其余不全\n",
    "### 测试数据中17条在gps中有记录,5条没有记录。\n",
    "- ['CNYTN-MXZLO',\n",
    "-  'CNSHK-SGSIN',\n",
    "- 'CNSHK-CLVAP',\n",
    "- 'CNYTN-ARENA',\n",
    "- 'CNYTN-MATNG',\n",
    "- 'CNSHK-PKQCT',\n",
    "- 'COBUN-HKHKG',\n",
    "- 'CNYTN-PAONX',\n",
    "- 'CNSHK-SIKOP',\n",
    "- 'CNYTN-CAVAN',\n",
    "- 'CNYTN-MTMLA',\n",
    "- 'CNSHK-ZADUR',\n",
    "- 'CNSHK-LBBEY',\n",
    "- 'CNYTN-RTM',\n",
    "- 'CNHKG-MXZLO',\n",
    "- 'CNYTN-NZAKL',\n",
    "- 'CNSHA-PAMIT']\n",
    " \n",
    " 没有记录的5条:\n",
    " ['CNSHK-MYTPP', 'CNSHK-GRPIR', 'CNSHK-ESALG', 'CNSHA-SGSIN', 'HKHKG-FRFOS']\n",
    " \n",
    " ## GPS中共有598条含有中转港的数据\n",
    " 如果算上有中转港的数据:\n",
    " ['CNSHK-MYTPP', 'CNSHK-GRPIR']在gps中也有出现\n",
    " [CNSHK-CNNSA-CIABJ-BJCOO-SGSIN-MYTPP,CNSHK-TRTEK-GRPIR]\n",
    " \n",
    "- CNYTN-EGPSE-MATNG\n",
    "- CNYTN-HKHKG-AUBNE-NZAKL\n",
    "- CNYTN-CNSHA-KRPUS-CAVAN\n",
    "- CNSHK-SGSIN-EGPSE-LBBEY\n",
    "- CNSHK-MYTPP-MUPLU-ZADUR\n",
    "- CNYTN-CNXAM-CNSGH-PAONX\n",
    "- CNSHK-SGSIN-MYWSP-ZADUR\n",
    "- CNSHK-SGSIN-EGPSD-LBBEY\n",
    "- CNSHK-SGSIN-EGSUZ-EGPSE-ILHFA-SIKOP\n",
    "- CNSHK-SGSIN-MTMLA-SIKOP\n",
    "- CNSHK-SGSIN-AEJEA-QAHMD-SADMM-OMSOH-SGSIN\n",
    "- CNYTN-TWKHH-CNSHA-CNNBG-MXZLO\n",
    "- CNYTN-TWKHH-PAPAN-PAONX\n",
    "- CNSHK-CNNSA-CIABJ-GHTEM-TGFLW-SGSIN\n",
    "- CNSHK-CNNSA-BJCOO-MYTPP-SGSIN\n",
    "- CNYTN-TWKHH-CAVAN\n",
    "- CNSHK-CNNSA-TGFLW-BFOUA-NAWVB-SGSIN\n",
    "- CNSHK-CNNSA-TGFLW-CMDLA-SGSIN\n",
    " "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['CNYTN-MXZLO',\n",
       " 'CNSHK-SGSIN',\n",
       " 'CNSHK-CLVAP',\n",
       " 'CNYTN-ARENA',\n",
       " 'CNYTN-MATNG',\n",
       " 'CNSHK-PKQCT',\n",
       " 'COBUN-HKHKG',\n",
       " 'CNYTN-PAONX',\n",
       " 'CNSHK-SIKOP',\n",
       " 'CNYTN-CAVAN',\n",
       " 'CNYTN-MTMLA',\n",
       " 'CNSHK-ZADUR',\n",
       " 'CNSHK-LBBEY',\n",
       " 'CNYTN-RTM',\n",
       " 'CNHKG-MXZLO',\n",
       " 'CNYTN-NZAKL',\n",
       " 'CNSHA-PAMIT']"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gpsdfx=pd.read_csv('train0523.csv',usecols=[12])\n",
    "gpsdfx.columns =['TRANSPORT_TRACE']\n",
    "test_trace=test_data['TRANSPORT_TRACE'].unique()\n",
    "train_trace=gpsdfx['TRANSPORT_TRACE'].unique()\n",
    "tmps=[]\n",
    "for i in test_trace:\n",
    "    if i in train_trace:\n",
    "        tmps.append(i)\n",
    "tmps"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "CNYTN-EGPSE-MATNG\n",
      "CNYTN-HKHKG-AUBNE-NZAKL\n",
      "CNYTN-CNSHA-KRPUS-CAVAN\n",
      "CNSHK-SGSIN-EGPSE-LBBEY\n",
      "CNSHK-MYTPP-MUPLU-ZADUR\n",
      "CNYTN-CNXAM-CNSGH-PAONX\n",
      "CNSHK-SGSIN-MYWSP-ZADUR\n",
      "CNSHK-SGSIN-EGPSD-LBBEY\n",
      "CNSHK-SGSIN-EGSUZ-EGPSE-ILHFA-SIKOP\n",
      "CNSHK-SGSIN-MTMLA-SIKOP\n",
      "CNSHK-SGSIN-AEJEA-QAHMD-SADMM-OMSOH-SGSIN\n",
      "CNYTN-TWKHH-CNSHA-CNNBG-MXZLO\n",
      "CNYTN-TWKHH-PAPAN-PAONX\n",
      "CNSHK-CNNSA-CIABJ-GHTEM-TGFLW-SGSIN\n",
      "CNSHK-CNNSA-BJCOO-MYTPP-SGSIN\n",
      "CNYTN-TWKHH-CAVAN\n",
      "CNSHK-CNNSA-TGFLW-BFOUA-NAWVB-SGSIN\n",
      "CNSHK-CNNSA-TGFLW-CMDLA-SGSIN\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#这段代码用于计算中转港有关\n",
    "cnt=0\n",
    "i=0\n",
    "for v in train_trace:\n",
    "    \n",
    "    if type(v)==str:\n",
    "        if '-' in v:\n",
    "            tmp=v.split('-')\n",
    "            if len(tmp)>2:\n",
    "                del_median=tmp[0]+'-'+tmp[-1]\n",
    "                if del_median in tmps:#['CNSHK-MYTPP', 'CNSHK-GRPIR', 'CNSHK-ESALG', 'CNSHA-SGSIN', 'HKHKG-FRFOS']:\n",
    "                    print(v)\n",
    "cnt"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##  历史运单事件数据. 历史运单事件数据描述每个运单在船运的过程中，与港口相关的关键信息，如离开起运港、到达目的港等\n",
    "- loadingOrder 运单号，与历史运单GPS数据中的loadingOrder字段一致\n",
    "- EVENT_CODE 事件编码，主要事件包括：\n",
    " - TRANSIT PORT ATD实际离开中转港\n",
    " - SHIPMENT ONBOARD DATE实际离开起运港\n",
    " - TRANSIT PORT ATA实际到达中转港\n",
    " - ARRIVAL AT PORT实际到达目的港\n",
    " - 注：部分船可能没有中转港\n",
    "- EVENT_LOCATION_ID 港口名称，对应“港口坐标数据”表中的字段TRANS_NODE_NAME\n",
    "- EVENT_CONVOLUTION_DATE  事件发生的时间，格式为：yyyy/MM/dd HH:mm:ss（dd与HH之间为两个空格）例如Event_code为“SHIPMENT ONBOARD DATE\"时，此字段表示船从起运港出发的时间。\n",
    "- EVENT_CODE为“ARRIVAL AT PORT\"时，此字段表示船到达目的港的时间。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 清洗event数据\n",
    "- event中具有ARRIVAL AT PORT的记录总共14538条,占比14538/158341\n",
    "- event中具有ARRIVAL AT PORT的运单总共有8926条，占比8926/15512.说明有的运单有多个ARRIVAL AT PORT时间,经过分析发现，感觉是因为有重复值出现和数据错误\n",
    "- 筛选出有出发记录和到达记录的运单(有且仅有一个出发/到达记录，并且到达时间晚于出发时间)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 138,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Administrator\\AppData\\Local\\Continuum\\anaconda3\\envs\\huawei\\lib\\site-packages\\IPython\\core\\interactiveshell.py:3063: DtypeWarning: Columns (0,1,2,3) have mixed types.Specify dtype option on import or set low_memory=False.\n",
      "  interactivity=interactivity, compiler=compiler, result=result)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "满足条件运单个数: 978\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>loadingOrder</th>\n",
       "      <th>EVENT_CODE</th>\n",
       "      <th>EVENT_LOCATION_ID</th>\n",
       "      <th>EVENT_CONVOLUTION_DATE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AB283635056094</td>\n",
       "      <td>SHIPMENT ONBOARD DATE</td>\n",
       "      <td>CNSHK</td>\n",
       "      <td>2020-04-16 22:08:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AB283635056094</td>\n",
       "      <td>ARRIVAL AT PORT</td>\n",
       "      <td>MYPKG</td>\n",
       "      <td>2020-04-20 21:41:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AC188113754775</td>\n",
       "      <td>SHIPMENT ONBOARD DATE</td>\n",
       "      <td>CNYTN</td>\n",
       "      <td>2019-04-02 12:53:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AC188113754775</td>\n",
       "      <td>ARRIVAL AT PORT</td>\n",
       "      <td>MXZLO</td>\n",
       "      <td>2019-04-08 11:11:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AE172923690170</td>\n",
       "      <td>SHIPMENT ONBOARD DATE</td>\n",
       "      <td>CNYTN</td>\n",
       "      <td>2020-03-18 02:23:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1951</th>\n",
       "      <td>[W849031957501</td>\n",
       "      <td>ARRIVAL AT PORT</td>\n",
       "      <td>MACAS</td>\n",
       "      <td>2020-04-12 22:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1952</th>\n",
       "      <td>[Y861332548397</td>\n",
       "      <td>SHIPMENT ONBOARD DATE</td>\n",
       "      <td>SIKOP</td>\n",
       "      <td>2020-02-26 18:02:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1953</th>\n",
       "      <td>[Y861332548397</td>\n",
       "      <td>ARRIVAL AT PORT</td>\n",
       "      <td>CNYTN</td>\n",
       "      <td>2020-04-06 23:50:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1954</th>\n",
       "      <td>[Z759280240591</td>\n",
       "      <td>SHIPMENT ONBOARD DATE</td>\n",
       "      <td>CNSHK</td>\n",
       "      <td>2020-03-14 17:19:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1955</th>\n",
       "      <td>[Z759280240591</td>\n",
       "      <td>ARRIVAL AT PORT</td>\n",
       "      <td>NGTIN</td>\n",
       "      <td>2020-04-16 06:30:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>1956 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        loadingOrder             EVENT_CODE EVENT_LOCATION_ID  \\\n",
       "0     AB283635056094  SHIPMENT ONBOARD DATE             CNSHK   \n",
       "1     AB283635056094        ARRIVAL AT PORT             MYPKG   \n",
       "2     AC188113754775  SHIPMENT ONBOARD DATE             CNYTN   \n",
       "3     AC188113754775        ARRIVAL AT PORT             MXZLO   \n",
       "4     AE172923690170  SHIPMENT ONBOARD DATE             CNYTN   \n",
       "...              ...                    ...               ...   \n",
       "1951  [W849031957501        ARRIVAL AT PORT             MACAS   \n",
       "1952  [Y861332548397  SHIPMENT ONBOARD DATE             SIKOP   \n",
       "1953  [Y861332548397        ARRIVAL AT PORT             CNYTN   \n",
       "1954  [Z759280240591  SHIPMENT ONBOARD DATE             CNSHK   \n",
       "1955  [Z759280240591        ARRIVAL AT PORT             NGTIN   \n",
       "\n",
       "     EVENT_CONVOLUTION_DATE  \n",
       "0       2020-04-16 22:08:00  \n",
       "1       2020-04-20 21:41:00  \n",
       "2       2019-04-02 12:53:00  \n",
       "3       2019-04-08 11:11:00  \n",
       "4       2020-03-18 02:23:00  \n",
       "...                     ...  \n",
       "1951    2020-04-12 22:00:00  \n",
       "1952    2020-02-26 18:02:00  \n",
       "1953    2020-04-06 23:50:00  \n",
       "1954    2020-03-14 17:19:00  \n",
       "1955    2020-04-16 06:30:00  \n",
       "\n",
       "[1956 rows x 4 columns]"
      ]
     },
     "execution_count": 138,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#event中具有ARRIVAL AT PORT的记录总共14538条,占比14538/158341\n",
    "#event中具有ARRIVAL AT PORT的运单总共有8926条，占比8926/15512.说明有的运单有多个ARRIVAL AT PORT时间\n",
    "#其中AY399952630533更是达到了17次出现ARRIVAL AT PORT，但经过分析，仿佛是由于数据重复和错误\n",
    "event=pd.read_csv('./event_port/loadingOrderEvent.csv')\n",
    "df_event=event[event['EVENT_CODE'].isin(['SHIPMENT ONBOARD DATE','ARRIVAL AT PORT'])].reset_index()\n",
    "df_event.sort_values(['loadingOrder', 'EVENT_CONVOLUTION_DATE'],inplace=True)\n",
    "df_event.drop(['index'],axis=1,inplace=True)\n",
    "df_event=df_event.reset_index(drop=True)\n",
    "df_event['EVENT_CONVOLUTION_DATE'] = pd.to_datetime(df_event['EVENT_CONVOLUTION_DATE'], infer_datetime_format=True)\n",
    "#下面的代码是筛选出有出发记录和到达记录的运单(有且仅有一个出发/到达记录，并且到达时间晚于出发时间)\n",
    "grouped=df_event.groupby('loadingOrder')\n",
    "reserve_order=[]\n",
    "for name,group in grouped:\n",
    "    if len(group['EVENT_CODE'])==2:\n",
    "        tmp=group['EVENT_CODE'].reset_index(drop=True)\n",
    "        tmp_time=group['EVENT_CONVOLUTION_DATE'].reset_index(drop=True)\n",
    "        if tmp[:1][0]=='SHIPMENT ONBOARD DATE' and tmp[-1:][1]=='ARRIVAL AT PORT' and tmp_time[:1][0]<tmp_time[-1:][1]:\n",
    "            reserve_order.append(group['loadingOrder'].reset_index(drop=True)[:1][0])\n",
    "            #print(group['loadingOrder'].reset_index(drop=True)[:1][0])\n",
    "print(\"满足条件运单个数:\",len(reserve_order))\n",
    "orders=df_event['loadingOrder'].values\n",
    "reserve_index=[]\n",
    "for index in range(len(orders)):\n",
    "    if orders[index] in reserve_order:\n",
    "        reserve_index.append(index)\n",
    "df_event=df_event.loc[reserve_index].reset_index(drop=True)\n",
    "df_event.to_csv('event_clean.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 156,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>loadingOrder</th>\n",
       "      <th>start_port</th>\n",
       "      <th>end_port</th>\n",
       "      <th>start_time</th>\n",
       "      <th>end_time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AB283635056094</td>\n",
       "      <td>CNSHK</td>\n",
       "      <td>MYPKG</td>\n",
       "      <td>2020-04-16 22:08:00</td>\n",
       "      <td>2020-04-20 21:41:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AC188113754775</td>\n",
       "      <td>CNYTN</td>\n",
       "      <td>MXZLO</td>\n",
       "      <td>2019-04-02 12:53:00</td>\n",
       "      <td>2019-04-08 11:11:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AE172923690170</td>\n",
       "      <td>CNYTN</td>\n",
       "      <td>RTM</td>\n",
       "      <td>2020-03-18 02:23:00</td>\n",
       "      <td>2020-04-10 14:46:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AF167947002003</td>\n",
       "      <td>CNSHK</td>\n",
       "      <td>THLCH</td>\n",
       "      <td>2019-11-25 23:59:00</td>\n",
       "      <td>2020-03-01 22:59:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AF842018574399</td>\n",
       "      <td>CNYTN</td>\n",
       "      <td>TRIZT</td>\n",
       "      <td>2020-01-14 18:20:00</td>\n",
       "      <td>2020-03-06 17:21:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>973</th>\n",
       "      <td>[V777965078223</td>\n",
       "      <td>CNSHK</td>\n",
       "      <td>PHMNL</td>\n",
       "      <td>2020-03-29 23:50:00</td>\n",
       "      <td>2020-04-06 23:50:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>974</th>\n",
       "      <td>[W782379188175</td>\n",
       "      <td>CNSHK</td>\n",
       "      <td>EGALY</td>\n",
       "      <td>2020-03-07 17:14:00</td>\n",
       "      <td>2020-04-19 17:20:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>975</th>\n",
       "      <td>[W849031957501</td>\n",
       "      <td>CNYTN</td>\n",
       "      <td>MACAS</td>\n",
       "      <td>2020-03-17 10:00:00</td>\n",
       "      <td>2020-04-12 22:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>976</th>\n",
       "      <td>[Y861332548397</td>\n",
       "      <td>SIKOP</td>\n",
       "      <td>CNYTN</td>\n",
       "      <td>2020-02-26 18:02:00</td>\n",
       "      <td>2020-04-06 23:50:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>977</th>\n",
       "      <td>[Z759280240591</td>\n",
       "      <td>CNSHK</td>\n",
       "      <td>NGTIN</td>\n",
       "      <td>2020-03-14 17:19:00</td>\n",
       "      <td>2020-04-16 06:30:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>978 rows × 5 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       loadingOrder start_port end_port          start_time  \\\n",
       "0    AB283635056094      CNSHK    MYPKG 2020-04-16 22:08:00   \n",
       "1    AC188113754775      CNYTN    MXZLO 2019-04-02 12:53:00   \n",
       "2    AE172923690170      CNYTN      RTM 2020-03-18 02:23:00   \n",
       "3    AF167947002003      CNSHK    THLCH 2019-11-25 23:59:00   \n",
       "4    AF842018574399      CNYTN    TRIZT 2020-01-14 18:20:00   \n",
       "..              ...        ...      ...                 ...   \n",
       "973  [V777965078223      CNSHK    PHMNL 2020-03-29 23:50:00   \n",
       "974  [W782379188175      CNSHK    EGALY 2020-03-07 17:14:00   \n",
       "975  [W849031957501      CNYTN    MACAS 2020-03-17 10:00:00   \n",
       "976  [Y861332548397      SIKOP    CNYTN 2020-02-26 18:02:00   \n",
       "977  [Z759280240591      CNSHK    NGTIN 2020-03-14 17:19:00   \n",
       "\n",
       "               end_time  \n",
       "0   2020-04-20 21:41:00  \n",
       "1   2019-04-08 11:11:00  \n",
       "2   2020-04-10 14:46:00  \n",
       "3   2020-03-01 22:59:00  \n",
       "4   2020-03-06 17:21:00  \n",
       "..                  ...  \n",
       "973 2020-04-06 23:50:00  \n",
       "974 2020-04-19 17:20:00  \n",
       "975 2020-04-12 22:00:00  \n",
       "976 2020-04-06 23:50:00  \n",
       "977 2020-04-16 06:30:00  \n",
       "\n",
       "[978 rows x 5 columns]"
      ]
     },
     "execution_count": 156,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#为了方便连接event和gps，这里将event数据重新组织\n",
    "df_empty = pd.DataFrame(columns=['loadingOrder', 'start_port', 'end_port', 'start_time','end_time']) \n",
    "event_order=[]\n",
    "event_start_port=[]\n",
    "event_end_port=[]\n",
    "event_start_time=[]\n",
    "event_end_time=[]\n",
    "for index,order,_,ID,time in df_event.itertuples():\n",
    "    if index%2==0:\n",
    "        event_order.append(order)\n",
    "        event_start_port.append(ID)\n",
    "        event_end_port.append(df_event['EVENT_LOCATION_ID'][index+1])\n",
    "        event_start_time.append(time)\n",
    "        event_end_time.append(df_event['EVENT_CONVOLUTION_DATE'][index+1])\n",
    "\n",
    "df_empty['loadingOrder']=event_order\n",
    "df_empty['start_port']=event_start_port\n",
    "df_empty['end_port']=event_end_port\n",
    "df_empty['start_time']=event_start_time\n",
    "df_empty['end_time']=event_end_time\n",
    "df_empty"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 下面需要将gps数据和event数据对应起来(按照loadingOrder连接)\n",
    "- 经过清洗:gps有7159条运单被保留，event中仅有978条"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 150,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0\n",
      "1000000\n",
      "2000000\n",
      "3000000\n",
      "4000000\n",
      "5000000\n",
      "6000000\n",
      "7000000\n",
      "8000000\n",
      "9000000\n",
      "10000000\n",
      "11000000\n",
      "12000000\n",
      "13000000\n",
      "14000000\n",
      "15000000\n",
      "16000000\n",
      "17000000\n",
      "18000000\n",
      "19000000\n",
      "20000000\n",
      "21000000\n",
      "22000000\n",
      "23000000\n",
      "24000000\n",
      "25000000\n",
      "26000000\n",
      "27000000\n",
      "28000000\n",
      "29000000\n",
      "30000000\n",
      "31000000\n",
      "32000000\n",
      "33000000\n",
      "34000000\n",
      "35000000\n",
      "36000000\n",
      "37000000\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>loadingOrder</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>longitude</th>\n",
       "      <th>latitude</th>\n",
       "      <th>speed</th>\n",
       "      <th>direction</th>\n",
       "      <th>TRANSPORT_TRACE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>QL937761313845</td>\n",
       "      <td>2019-01-10T16:16:54.000Z</td>\n",
       "      <td>113.890020</td>\n",
       "      <td>22.449807</td>\n",
       "      <td>0</td>\n",
       "      <td>3770</td>\n",
       "      <td>CNSHK-HRRJK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>QL937761313845</td>\n",
       "      <td>2019-01-10T16:31:53.000Z</td>\n",
       "      <td>113.890040</td>\n",
       "      <td>22.449805</td>\n",
       "      <td>0</td>\n",
       "      <td>3760</td>\n",
       "      <td>CNSHK-HRRJK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>QL937761313845</td>\n",
       "      <td>2019-01-10T16:46:53.000Z</td>\n",
       "      <td>113.890042</td>\n",
       "      <td>22.449810</td>\n",
       "      <td>0</td>\n",
       "      <td>3700</td>\n",
       "      <td>CNSHK-HRRJK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>QL937761313845</td>\n",
       "      <td>2019-01-10T17:19:54.000Z</td>\n",
       "      <td>113.890053</td>\n",
       "      <td>22.449830</td>\n",
       "      <td>0</td>\n",
       "      <td>3720</td>\n",
       "      <td>CNSHK-HRRJK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>QL937761313845</td>\n",
       "      <td>2019-01-10T17:37:53.000Z</td>\n",
       "      <td>113.890037</td>\n",
       "      <td>22.449823</td>\n",
       "      <td>0</td>\n",
       "      <td>3770</td>\n",
       "      <td>CNSHK-HRRJK</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2002095</th>\n",
       "      <td>SY689544812125</td>\n",
       "      <td>2020-04-30T01:36:29.000Z</td>\n",
       "      <td>120.512892</td>\n",
       "      <td>25.716412</td>\n",
       "      <td>28</td>\n",
       "      <td>2710</td>\n",
       "      <td>CNYTN-CLSAN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2002096</th>\n",
       "      <td>SY689544812125</td>\n",
       "      <td>2020-04-30T01:39:37.000Z</td>\n",
       "      <td>120.519463</td>\n",
       "      <td>25.728125</td>\n",
       "      <td>28</td>\n",
       "      <td>2690</td>\n",
       "      <td>CNYTN-CLSAN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2002097</th>\n",
       "      <td>SY689544812125</td>\n",
       "      <td>2020-04-30T01:42:01.000Z</td>\n",
       "      <td>120.524593</td>\n",
       "      <td>25.737330</td>\n",
       "      <td>28</td>\n",
       "      <td>2680</td>\n",
       "      <td>CNYTN-CLSAN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2002098</th>\n",
       "      <td>SY689544812125</td>\n",
       "      <td>2020-04-30T01:47:14.000Z</td>\n",
       "      <td>120.535722</td>\n",
       "      <td>25.757097</td>\n",
       "      <td>28</td>\n",
       "      <td>2870</td>\n",
       "      <td>CNYTN-CLSAN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2002099</th>\n",
       "      <td>SY689544812125</td>\n",
       "      <td>2020-04-30T01:50:25.000Z</td>\n",
       "      <td>120.543330</td>\n",
       "      <td>25.768930</td>\n",
       "      <td>28</td>\n",
       "      <td>3100</td>\n",
       "      <td>CNYTN-CLSAN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2002100 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           loadingOrder                 timestamp   longitude   latitude  \\\n",
       "0        QL937761313845  2019-01-10T16:16:54.000Z  113.890020  22.449807   \n",
       "1        QL937761313845  2019-01-10T16:31:53.000Z  113.890040  22.449805   \n",
       "2        QL937761313845  2019-01-10T16:46:53.000Z  113.890042  22.449810   \n",
       "3        QL937761313845  2019-01-10T17:19:54.000Z  113.890053  22.449830   \n",
       "4        QL937761313845  2019-01-10T17:37:53.000Z  113.890037  22.449823   \n",
       "...                 ...                       ...         ...        ...   \n",
       "2002095  SY689544812125  2020-04-30T01:36:29.000Z  120.512892  25.716412   \n",
       "2002096  SY689544812125  2020-04-30T01:39:37.000Z  120.519463  25.728125   \n",
       "2002097  SY689544812125  2020-04-30T01:42:01.000Z  120.524593  25.737330   \n",
       "2002098  SY689544812125  2020-04-30T01:47:14.000Z  120.535722  25.757097   \n",
       "2002099  SY689544812125  2020-04-30T01:50:25.000Z  120.543330  25.768930   \n",
       "\n",
       "         speed  direction TRANSPORT_TRACE  \n",
       "0            0       3770     CNSHK-HRRJK  \n",
       "1            0       3760     CNSHK-HRRJK  \n",
       "2            0       3700     CNSHK-HRRJK  \n",
       "3            0       3720     CNSHK-HRRJK  \n",
       "4            0       3770     CNSHK-HRRJK  \n",
       "...        ...        ...             ...  \n",
       "2002095     28       2710     CNYTN-CLSAN  \n",
       "2002096     28       2690     CNYTN-CLSAN  \n",
       "2002097     28       2680     CNYTN-CLSAN  \n",
       "2002098     28       2870     CNYTN-CLSAN  \n",
       "2002099     28       3100     CNYTN-CLSAN  \n",
       "\n",
       "[2002100 rows x 7 columns]"
      ]
     },
     "execution_count": 150,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#下面筛选出gps和event中共有的运单数据,得到的是最终的gps数据,但是这样筛选出来只有388条运单，这似乎是不太够的\n",
    "event_order=df_event['loadingOrder'].unique()\n",
    "gps_order=clean_df['loadingOrder'].values\n",
    "reserve_index=[]\n",
    "for index in range(len(gps_order)):\n",
    "    if index%5000000==0:\n",
    "        print(index)\n",
    "    #print(gps_order[index])\n",
    "    if gps_order[index] in event_order:\n",
    "        reserve_index.append(index)\n",
    "gps_event=clean_df.loc[reserve_index].reset_index(drop=True)\n",
    "gps_event.to_csv('gps_event200w.csv',index=False)#"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 152,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "count            2002100\n",
       "unique               388\n",
       "top       JN852747112402\n",
       "freq               47827\n",
       "Name: loadingOrder, dtype: object"
      ]
     },
     "execution_count": 152,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gps_event['loadingOrder'].describe()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 158,
   "metadata": {},
   "outputs": [],
   "source": [
    "gps_event388=gps_event.merge(df_empty, on='loadingOrder', how='left')\n",
    "gps_event388.to_csv('gps_event388.csv',index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 159,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>loadingOrder</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>longitude</th>\n",
       "      <th>latitude</th>\n",
       "      <th>speed</th>\n",
       "      <th>direction</th>\n",
       "      <th>TRANSPORT_TRACE</th>\n",
       "      <th>start_port</th>\n",
       "      <th>end_port</th>\n",
       "      <th>start_time</th>\n",
       "      <th>end_time</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>QL937761313845</td>\n",
       "      <td>2019-01-10T16:16:54.000Z</td>\n",
       "      <td>113.890020</td>\n",
       "      <td>22.449807</td>\n",
       "      <td>0</td>\n",
       "      <td>3770</td>\n",
       "      <td>CNSHK-HRRJK</td>\n",
       "      <td>SHEKOU</td>\n",
       "      <td>SURABAYA</td>\n",
       "      <td>2019-01-25 09:00:00</td>\n",
       "      <td>2019-01-30 17:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>QL937761313845</td>\n",
       "      <td>2019-01-10T16:31:53.000Z</td>\n",
       "      <td>113.890040</td>\n",
       "      <td>22.449805</td>\n",
       "      <td>0</td>\n",
       "      <td>3760</td>\n",
       "      <td>CNSHK-HRRJK</td>\n",
       "      <td>SHEKOU</td>\n",
       "      <td>SURABAYA</td>\n",
       "      <td>2019-01-25 09:00:00</td>\n",
       "      <td>2019-01-30 17:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>QL937761313845</td>\n",
       "      <td>2019-01-10T16:46:53.000Z</td>\n",
       "      <td>113.890042</td>\n",
       "      <td>22.449810</td>\n",
       "      <td>0</td>\n",
       "      <td>3700</td>\n",
       "      <td>CNSHK-HRRJK</td>\n",
       "      <td>SHEKOU</td>\n",
       "      <td>SURABAYA</td>\n",
       "      <td>2019-01-25 09:00:00</td>\n",
       "      <td>2019-01-30 17:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>QL937761313845</td>\n",
       "      <td>2019-01-10T17:19:54.000Z</td>\n",
       "      <td>113.890053</td>\n",
       "      <td>22.449830</td>\n",
       "      <td>0</td>\n",
       "      <td>3720</td>\n",
       "      <td>CNSHK-HRRJK</td>\n",
       "      <td>SHEKOU</td>\n",
       "      <td>SURABAYA</td>\n",
       "      <td>2019-01-25 09:00:00</td>\n",
       "      <td>2019-01-30 17:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>QL937761313845</td>\n",
       "      <td>2019-01-10T17:37:53.000Z</td>\n",
       "      <td>113.890037</td>\n",
       "      <td>22.449823</td>\n",
       "      <td>0</td>\n",
       "      <td>3770</td>\n",
       "      <td>CNSHK-HRRJK</td>\n",
       "      <td>SHEKOU</td>\n",
       "      <td>SURABAYA</td>\n",
       "      <td>2019-01-25 09:00:00</td>\n",
       "      <td>2019-01-30 17:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2002095</th>\n",
       "      <td>SY689544812125</td>\n",
       "      <td>2020-04-30T01:36:29.000Z</td>\n",
       "      <td>120.512892</td>\n",
       "      <td>25.716412</td>\n",
       "      <td>28</td>\n",
       "      <td>2710</td>\n",
       "      <td>CNYTN-CLSAN</td>\n",
       "      <td>CNYTN</td>\n",
       "      <td>SIKOP</td>\n",
       "      <td>2020-02-23 09:48:00</td>\n",
       "      <td>2020-02-28 03:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2002096</th>\n",
       "      <td>SY689544812125</td>\n",
       "      <td>2020-04-30T01:39:37.000Z</td>\n",
       "      <td>120.519463</td>\n",
       "      <td>25.728125</td>\n",
       "      <td>28</td>\n",
       "      <td>2690</td>\n",
       "      <td>CNYTN-CLSAN</td>\n",
       "      <td>CNYTN</td>\n",
       "      <td>SIKOP</td>\n",
       "      <td>2020-02-23 09:48:00</td>\n",
       "      <td>2020-02-28 03:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2002097</th>\n",
       "      <td>SY689544812125</td>\n",
       "      <td>2020-04-30T01:42:01.000Z</td>\n",
       "      <td>120.524593</td>\n",
       "      <td>25.737330</td>\n",
       "      <td>28</td>\n",
       "      <td>2680</td>\n",
       "      <td>CNYTN-CLSAN</td>\n",
       "      <td>CNYTN</td>\n",
       "      <td>SIKOP</td>\n",
       "      <td>2020-02-23 09:48:00</td>\n",
       "      <td>2020-02-28 03:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2002098</th>\n",
       "      <td>SY689544812125</td>\n",
       "      <td>2020-04-30T01:47:14.000Z</td>\n",
       "      <td>120.535722</td>\n",
       "      <td>25.757097</td>\n",
       "      <td>28</td>\n",
       "      <td>2870</td>\n",
       "      <td>CNYTN-CLSAN</td>\n",
       "      <td>CNYTN</td>\n",
       "      <td>SIKOP</td>\n",
       "      <td>2020-02-23 09:48:00</td>\n",
       "      <td>2020-02-28 03:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2002099</th>\n",
       "      <td>SY689544812125</td>\n",
       "      <td>2020-04-30T01:50:25.000Z</td>\n",
       "      <td>120.543330</td>\n",
       "      <td>25.768930</td>\n",
       "      <td>28</td>\n",
       "      <td>3100</td>\n",
       "      <td>CNYTN-CLSAN</td>\n",
       "      <td>CNYTN</td>\n",
       "      <td>SIKOP</td>\n",
       "      <td>2020-02-23 09:48:00</td>\n",
       "      <td>2020-02-28 03:00:00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2002100 rows × 11 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "           loadingOrder                 timestamp   longitude   latitude  \\\n",
       "0        QL937761313845  2019-01-10T16:16:54.000Z  113.890020  22.449807   \n",
       "1        QL937761313845  2019-01-10T16:31:53.000Z  113.890040  22.449805   \n",
       "2        QL937761313845  2019-01-10T16:46:53.000Z  113.890042  22.449810   \n",
       "3        QL937761313845  2019-01-10T17:19:54.000Z  113.890053  22.449830   \n",
       "4        QL937761313845  2019-01-10T17:37:53.000Z  113.890037  22.449823   \n",
       "...                 ...                       ...         ...        ...   \n",
       "2002095  SY689544812125  2020-04-30T01:36:29.000Z  120.512892  25.716412   \n",
       "2002096  SY689544812125  2020-04-30T01:39:37.000Z  120.519463  25.728125   \n",
       "2002097  SY689544812125  2020-04-30T01:42:01.000Z  120.524593  25.737330   \n",
       "2002098  SY689544812125  2020-04-30T01:47:14.000Z  120.535722  25.757097   \n",
       "2002099  SY689544812125  2020-04-30T01:50:25.000Z  120.543330  25.768930   \n",
       "\n",
       "         speed  direction TRANSPORT_TRACE start_port  end_port  \\\n",
       "0            0       3770     CNSHK-HRRJK     SHEKOU  SURABAYA   \n",
       "1            0       3760     CNSHK-HRRJK     SHEKOU  SURABAYA   \n",
       "2            0       3700     CNSHK-HRRJK     SHEKOU  SURABAYA   \n",
       "3            0       3720     CNSHK-HRRJK     SHEKOU  SURABAYA   \n",
       "4            0       3770     CNSHK-HRRJK     SHEKOU  SURABAYA   \n",
       "...        ...        ...             ...        ...       ...   \n",
       "2002095     28       2710     CNYTN-CLSAN      CNYTN     SIKOP   \n",
       "2002096     28       2690     CNYTN-CLSAN      CNYTN     SIKOP   \n",
       "2002097     28       2680     CNYTN-CLSAN      CNYTN     SIKOP   \n",
       "2002098     28       2870     CNYTN-CLSAN      CNYTN     SIKOP   \n",
       "2002099     28       3100     CNYTN-CLSAN      CNYTN     SIKOP   \n",
       "\n",
       "                 start_time            end_time  \n",
       "0       2019-01-25 09:00:00 2019-01-30 17:00:00  \n",
       "1       2019-01-25 09:00:00 2019-01-30 17:00:00  \n",
       "2       2019-01-25 09:00:00 2019-01-30 17:00:00  \n",
       "3       2019-01-25 09:00:00 2019-01-30 17:00:00  \n",
       "4       2019-01-25 09:00:00 2019-01-30 17:00:00  \n",
       "...                     ...                 ...  \n",
       "2002095 2020-02-23 09:48:00 2020-02-28 03:00:00  \n",
       "2002096 2020-02-23 09:48:00 2020-02-28 03:00:00  \n",
       "2002097 2020-02-23 09:48:00 2020-02-28 03:00:00  \n",
       "2002098 2020-02-23 09:48:00 2020-02-28 03:00:00  \n",
       "2002099 2020-02-23 09:48:00 2020-02-28 03:00:00  \n",
       "\n",
       "[2002100 rows x 11 columns]"
      ]
     },
     "execution_count": 159,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gps_event388"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 162,
   "metadata": {},
   "outputs": [
    {
     "ename": "SyntaxError",
     "evalue": "invalid syntax (<ipython-input-162-bd5c7b2dc9b1>, line 13)",
     "output_type": "error",
     "traceback": [
      "\u001b[1;36m  File \u001b[1;32m\"<ipython-input-162-bd5c7b2dc9b1>\"\u001b[1;36m, line \u001b[1;32m13\u001b[0m\n\u001b[1;33m    if start_port in temp_dic:\u001b[0m\n\u001b[1;37m     ^\u001b[0m\n\u001b[1;31mSyntaxError\u001b[0m\u001b[1;31m:\u001b[0m invalid syntax\n"
     ]
    }
   ],
   "source": [
    "#下面是将gps和event进行连接，主要是起点终点的名字+gps坐标+出发时间和到达时间\n",
    "def convert_name_xy(name):#输入港口名称\n",
    "    port_name=port[port['TRANS_NODE_NAME'].isin([name])].reset_index()\n",
    "    return port_name['LONGITUDE'][0],port_name['LATITUDE'][0]#返回港口经纬度\n",
    "#  \n",
    "start_x=[]#起点\n",
    "start_y=[]#起点\n",
    "end_x=[]#终点\n",
    "end_y=[]#终点\n",
    "#存储中间结果,避免重复计算\n",
    "temp_dic={}\n",
    "for index,start_port,end_port in gps_event388[['start_port','end_port'].itertuples()\n",
    "for index,start_port,end_port in gps_event388[['start_port','end_port'].itertuples():\n",
    "    if start_port in temp_dic:\n",
    "        re=temp_dic[start_port]\n",
    "    else:\n",
    "        re=convert_name_xy(start_port)\n",
    "        temp_dic[start_port]=re\n",
    "    start_x.append(re[0])\n",
    "    start_y.append(re[1])\n",
    "    if end_port in temp_dic:\n",
    "        re=temp_dic[end_port]\n",
    "    else:\n",
    "        re=convert_name_xy(end_port)\n",
    "        temp_dic[end_port]=re\n",
    "    end_x.append(re[0])\n",
    "    end_y.append(re[1])\n",
    "gps_event388['start_x']=start_x\n",
    "gps_event388['start_y']=start_y\n",
    "gps_event388['end_x']=end_x\n",
    "gps_event388['end_y']=end_y"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 测试运单数据\n",
    "- loadingOrder 运单的运单号\n",
    "- timestamp 运单当前所处位置（经度、维度）的时间，格式为：yyyy-MM-dd'T'HH:mm:ss.SSSZ，如2019-09-05T16:33:17.000Z\n",
    "- longitude 运单承运船舶的当前经度：114.234567 \n",
    "- latitude 运单承运船舶的当前纬度：21.234567\n",
    "- speed 货物在运输过程中，当前船舶的瞬时速度，部分数据未提供的可自行计算。\n",
    "- direction 当前船舶的行驶方向，正北是0度，31480代表西北方向314.80度，900代表正东偏南9度。\n",
    "- carrierName 承运商名称，类似快递公司名称\n",
    "- vesselMMSI 脱敏后的船舶海上移动业务识别码MMSI， 唯一标识，对应到每一艘船\n",
    "- onboardDate 离开起运港时间，格式为：yyyy/MM/dd HH:mm:ss（dd与HH之间为两个空格），如2019/09/05 16:33:17\n",
    "- TRANSPORT_TRACE 船的路由，由“-”连接组成，例如CNSHK-MYPKG-MYTPP。由承运商预先录入，实际小概率存在不按此路由行驶（如遇塞港时），但最终会到达目的港口。"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>loadingOrder</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>longitude</th>\n",
       "      <th>latitude</th>\n",
       "      <th>speed</th>\n",
       "      <th>direction</th>\n",
       "      <th>carrierName</th>\n",
       "      <th>vesselMMSI</th>\n",
       "      <th>onboardDate</th>\n",
       "      <th>TRANSPORT_TRACE</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>CF946210847851</td>\n",
       "      <td>2019-04-02T02:42:28.000Z</td>\n",
       "      <td>138.471062</td>\n",
       "      <td>40.278787</td>\n",
       "      <td>31</td>\n",
       "      <td>5800</td>\n",
       "      <td>OIEQNT</td>\n",
       "      <td>R5480015614</td>\n",
       "      <td>2019/04/02  02:42:28</td>\n",
       "      <td>CNYTN-MXZLO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>CF946210847851</td>\n",
       "      <td>2019-04-02T02:59:28.000Z</td>\n",
       "      <td>138.552168</td>\n",
       "      <td>40.327785</td>\n",
       "      <td>30</td>\n",
       "      <td>4600</td>\n",
       "      <td>OIEQNT</td>\n",
       "      <td>R5480015614</td>\n",
       "      <td>2019/04/02  02:42:28</td>\n",
       "      <td>CNYTN-MXZLO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>CF946210847851</td>\n",
       "      <td>2019-04-02T03:07:28.000Z</td>\n",
       "      <td>138.588250</td>\n",
       "      <td>40.352542</td>\n",
       "      <td>30</td>\n",
       "      <td>4900</td>\n",
       "      <td>OIEQNT</td>\n",
       "      <td>R5480015614</td>\n",
       "      <td>2019/04/02  02:42:28</td>\n",
       "      <td>CNYTN-MXZLO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>CF946210847851</td>\n",
       "      <td>2019-04-02T03:43:28.000Z</td>\n",
       "      <td>138.751325</td>\n",
       "      <td>40.459447</td>\n",
       "      <td>30</td>\n",
       "      <td>5000</td>\n",
       "      <td>OIEQNT</td>\n",
       "      <td>R5480015614</td>\n",
       "      <td>2019/04/02  02:42:28</td>\n",
       "      <td>CNYTN-MXZLO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>CF946210847851</td>\n",
       "      <td>2019-04-02T04:29:28.000Z</td>\n",
       "      <td>138.969782</td>\n",
       "      <td>40.581485</td>\n",
       "      <td>30</td>\n",
       "      <td>5000</td>\n",
       "      <td>OIEQNT</td>\n",
       "      <td>R5480015614</td>\n",
       "      <td>2019/04/02  02:42:28</td>\n",
       "      <td>CNYTN-MXZLO</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     loadingOrder                 timestamp   longitude   latitude  speed  \\\n",
       "0  CF946210847851  2019-04-02T02:42:28.000Z  138.471062  40.278787     31   \n",
       "1  CF946210847851  2019-04-02T02:59:28.000Z  138.552168  40.327785     30   \n",
       "2  CF946210847851  2019-04-02T03:07:28.000Z  138.588250  40.352542     30   \n",
       "3  CF946210847851  2019-04-02T03:43:28.000Z  138.751325  40.459447     30   \n",
       "4  CF946210847851  2019-04-02T04:29:28.000Z  138.969782  40.581485     30   \n",
       "\n",
       "   direction carrierName   vesselMMSI           onboardDate TRANSPORT_TRACE  \n",
       "0       5800      OIEQNT  R5480015614  2019/04/02  02:42:28     CNYTN-MXZLO  \n",
       "1       4600      OIEQNT  R5480015614  2019/04/02  02:42:28     CNYTN-MXZLO  \n",
       "2       4900      OIEQNT  R5480015614  2019/04/02  02:42:28     CNYTN-MXZLO  \n",
       "3       5000      OIEQNT  R5480015614  2019/04/02  02:42:28     CNYTN-MXZLO  \n",
       "4       5000      OIEQNT  R5480015614  2019/04/02  02:42:28     CNYTN-MXZLO  "
      ]
     },
     "execution_count": 110,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test_data_path = 'A_testData0531.csv'\n",
    "test_data=pd.read_csv(test_data_path)\n",
    "test_data.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 111,
   "metadata": {},
   "outputs": [],
   "source": [
    "#test_data.TRANSPORT_TRACE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 112,
   "metadata": {},
   "outputs": [],
   "source": [
    "def convert_name_xy(name):#输入港口名称\n",
    "    port_name=port[port['TRANS_NODE_NAME'].isin([name])].reset_index()\n",
    "    return port_name['LONGITUDE'][0],port_name['LATITUDE'][0]#返回港口经纬度\n",
    "#  \n",
    "start_x=[]#起点\n",
    "start_y=[]#起点\n",
    "end_x=[]#终点\n",
    "end_y=[]#终点\n",
    "#存储中间结果,避免重复计算\n",
    "temp_dic={}\n",
    "for value in test_data['TRANSPORT_TRACE']:\n",
    "    s_e=value.split('-')\n",
    "    start_port=s_e[0]\n",
    "    end_port=s_e[1]\n",
    "    if start_port in temp_dic:\n",
    "        re=temp_dic[start_port]\n",
    "    else:\n",
    "        re=convert_name_xy(value.split('-')[0])\n",
    "        temp_dic[start_port]=re\n",
    "    start_x.append(re[0])\n",
    "    start_y.append(re[1])\n",
    "    if end_port in temp_dic:\n",
    "        re=temp_dic[end_port]\n",
    "    else:\n",
    "        re=convert_name_xy(value.split('-')[1])\n",
    "        temp_dic[end_port]=re\n",
    "    end_x.append(re[0])\n",
    "    end_y.append(re[1])\n",
    "test_data['start_x']=start_x\n",
    "test_data['start_y']=start_y\n",
    "test_data['end_x']=end_x\n",
    "test_data['end_y']=end_y"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 113,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>loadingOrder</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>longitude</th>\n",
       "      <th>latitude</th>\n",
       "      <th>speed</th>\n",
       "      <th>direction</th>\n",
       "      <th>carrierName</th>\n",
       "      <th>vesselMMSI</th>\n",
       "      <th>onboardDate</th>\n",
       "      <th>TRANSPORT_TRACE</th>\n",
       "      <th>start_x</th>\n",
       "      <th>start_y</th>\n",
       "      <th>end_x</th>\n",
       "      <th>end_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>CF946210847851</td>\n",
       "      <td>2019-04-02T02:42:28.000Z</td>\n",
       "      <td>138.471062</td>\n",
       "      <td>40.278787</td>\n",
       "      <td>31</td>\n",
       "      <td>5800</td>\n",
       "      <td>OIEQNT</td>\n",
       "      <td>R5480015614</td>\n",
       "      <td>2019/04/02  02:42:28</td>\n",
       "      <td>CNYTN-MXZLO</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-104.305571</td>\n",
       "      <td>19.085961</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>CF946210847851</td>\n",
       "      <td>2019-04-02T02:59:28.000Z</td>\n",
       "      <td>138.552168</td>\n",
       "      <td>40.327785</td>\n",
       "      <td>30</td>\n",
       "      <td>4600</td>\n",
       "      <td>OIEQNT</td>\n",
       "      <td>R5480015614</td>\n",
       "      <td>2019/04/02  02:42:28</td>\n",
       "      <td>CNYTN-MXZLO</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-104.305571</td>\n",
       "      <td>19.085961</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     loadingOrder                 timestamp   longitude   latitude  speed  \\\n",
       "0  CF946210847851  2019-04-02T02:42:28.000Z  138.471062  40.278787     31   \n",
       "1  CF946210847851  2019-04-02T02:59:28.000Z  138.552168  40.327785     30   \n",
       "\n",
       "   direction carrierName   vesselMMSI           onboardDate TRANSPORT_TRACE  \\\n",
       "0       5800      OIEQNT  R5480015614  2019/04/02  02:42:28     CNYTN-MXZLO   \n",
       "1       4600      OIEQNT  R5480015614  2019/04/02  02:42:28     CNYTN-MXZLO   \n",
       "\n",
       "      start_x  start_y       end_x      end_y  \n",
       "0  114.275347  22.5777 -104.305571  19.085961  \n",
       "1  114.275347  22.5777 -104.305571  19.085961  "
      ]
     },
     "execution_count": 113,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "test_data.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "metadata": {},
   "outputs": [],
   "source": [
    "#\n",
    "data=test_data\n",
    "data['temp_timestamp'] = data['timestamp']#当前时间\n",
    "data['onboardDate'] = pd.to_datetime(data['onboardDate'], infer_datetime_format=True)#离开起运港时间\n",
    "data['timestamp'] = pd.to_datetime(data['timestamp'], infer_datetime_format=True)\n",
    "data['longitude'] = data['longitude'].astype(float)\n",
    "data['loadingOrder'] = data['loadingOrder'].astype(str)\n",
    "data['latitude'] = data['latitude'].astype(float)\n",
    "data['speed'] = data['speed'].astype(float)\n",
    "data['direction'] = data['direction'].astype(float)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 136,
   "metadata": {},
   "outputs": [],
   "source": [
    "df=data\n",
    "data.sort_values(['loadingOrder', 'timestamp'], inplace=True)#按照订单和当前时间排序\n",
    "#下面是重点:计算该订单当前时间之前的所有轨迹的特征\n",
    "# 特征只选择经纬度、速度\\方向\n",
    "df['lat_diff'] = df.groupby('loadingOrder')['latitude'].diff(1)#经度变化\n",
    "df['lon_diff'] = df.groupby('loadingOrder')['longitude'].diff(1)#纬度变化\n",
    "df['speed_diff'] = df.groupby('loadingOrder')['speed'].diff(1)#速度变化量\n",
    "df['diff_minutes'] = df.groupby('loadingOrder')['timestamp'].diff(1).dt.total_seconds() // 60#记录之间的间隔时间(单位:s)\n",
    "#经纬度变化;速度变化，间隔时间小于一定阈值的认定为船没有动\n",
    "df['anchor'] = df.apply(lambda x: 1 if x['lat_diff'] <= 0.03 and x['lon_diff'] <= 0.03\n",
    "                        and x['speed_diff'] <= 0.3 and x['diff_minutes'] <= 10 else 0, axis=1)\n",
    "#对于每一个运单,记录条数(count)\n",
    "group_df = df.groupby('loadingOrder')['timestamp'].agg([('count','count')]).reset_index()\n",
    "#在所有的记录中，多少记录是停船状态\n",
    "#anchors=df.groupby('loadingOrder')['anchor']\n",
    "#sum_anchors=[]\n",
    "#for _,group in anchors:#这里求的是从起点到当前点的累计和，而不是总和(避免数据泄露)\n",
    "    #sum_anchors+=[sum(group[:v]) for v in range(1,len(group)+1)]\n",
    "#df['anchor_cnt']=sum_anchors\n",
    "#以经纬,度,速度，方向为基础特征，然后计算最小，最大，均值，终值等统计特性（以运单为单位）\n",
    "agg_function = ['min', 'max', 'mean', 'median']\n",
    "agg_col = ['latitude', 'longitude', 'speed', 'direction']\n",
    "\n",
    "group = df.groupby('loadingOrder')[agg_col].agg(agg_function).reset_index()\n",
    "#anchor_df.columns = ['loadingOrder', 'anchor_cnt']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 144,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>index</th>\n",
       "      <th>TRANS_NODE_NAME</th>\n",
       "      <th>LONGITUDE</th>\n",
       "      <th>LATITUDE</th>\n",
       "      <th>COUNTRY</th>\n",
       "      <th>STATE</th>\n",
       "      <th>CITY</th>\n",
       "      <th>REGION</th>\n",
       "      <th>ADDRESS</th>\n",
       "      <th>PORT_CODE</th>\n",
       "      <th>TRANSPORT_NODE_ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>535</td>\n",
       "      <td>MXZLO</td>\n",
       "      <td>-104.305571</td>\n",
       "      <td>19.085961</td>\n",
       "      <td>Mexico</td>\n",
       "      <td>Colima</td>\n",
       "      <td>Manzanillo</td>\n",
       "      <td>Vista de Mar II</td>\n",
       "      <td>Blvd. Miguel de la Madrid 750, Vista de Mar II...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2844000.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   index TRANS_NODE_NAME   LONGITUDE   LATITUDE COUNTRY   STATE        CITY  \\\n",
       "0    535           MXZLO -104.305571  19.085961  Mexico  Colima  Manzanillo   \n",
       "\n",
       "            REGION                                            ADDRESS  \\\n",
       "0  Vista de Mar II  Blvd. Miguel de la Madrid 750, Vista de Mar II...   \n",
       "\n",
       "   PORT_CODE  TRANSPORT_NODE_ID  \n",
       "0        NaN          2844000.0  "
      ]
     },
     "execution_count": 144,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "port[port['TRANS_NODE_NAME'].isin(['MXZLO'])].reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 108,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>loadingOrder</th>\n",
       "      <th>timestamp</th>\n",
       "      <th>longitude</th>\n",
       "      <th>latitude</th>\n",
       "      <th>speed</th>\n",
       "      <th>direction</th>\n",
       "      <th>carrierName</th>\n",
       "      <th>vesselMMSI</th>\n",
       "      <th>onboardDate</th>\n",
       "      <th>TRANSPORT_TRACE</th>\n",
       "      <th>start_x</th>\n",
       "      <th>start_y</th>\n",
       "      <th>end_x</th>\n",
       "      <th>end_y</th>\n",
       "      <th>temp_timestamp</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>CF946210847851</td>\n",
       "      <td>2019-04-02 02:42:28</td>\n",
       "      <td>138.471062</td>\n",
       "      <td>40.278787</td>\n",
       "      <td>31.0</td>\n",
       "      <td>5800.0</td>\n",
       "      <td>OIEQNT</td>\n",
       "      <td>R5480015614</td>\n",
       "      <td>2019-04-02 02:42:28</td>\n",
       "      <td>CNYTN-MXZLO</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-104.305571</td>\n",
       "      <td>19.085961</td>\n",
       "      <td>2019-04-02T02:42:28.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>CF946210847851</td>\n",
       "      <td>2019-04-02 02:59:28</td>\n",
       "      <td>138.552168</td>\n",
       "      <td>40.327785</td>\n",
       "      <td>30.0</td>\n",
       "      <td>4600.0</td>\n",
       "      <td>OIEQNT</td>\n",
       "      <td>R5480015614</td>\n",
       "      <td>2019-04-02 02:42:28</td>\n",
       "      <td>CNYTN-MXZLO</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-104.305571</td>\n",
       "      <td>19.085961</td>\n",
       "      <td>2019-04-02T02:59:28.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>CF946210847851</td>\n",
       "      <td>2019-04-02 03:07:28</td>\n",
       "      <td>138.588250</td>\n",
       "      <td>40.352542</td>\n",
       "      <td>30.0</td>\n",
       "      <td>4900.0</td>\n",
       "      <td>OIEQNT</td>\n",
       "      <td>R5480015614</td>\n",
       "      <td>2019-04-02 02:42:28</td>\n",
       "      <td>CNYTN-MXZLO</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-104.305571</td>\n",
       "      <td>19.085961</td>\n",
       "      <td>2019-04-02T03:07:28.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>CF946210847851</td>\n",
       "      <td>2019-04-02 03:43:28</td>\n",
       "      <td>138.751325</td>\n",
       "      <td>40.459447</td>\n",
       "      <td>30.0</td>\n",
       "      <td>5000.0</td>\n",
       "      <td>OIEQNT</td>\n",
       "      <td>R5480015614</td>\n",
       "      <td>2019-04-02 02:42:28</td>\n",
       "      <td>CNYTN-MXZLO</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-104.305571</td>\n",
       "      <td>19.085961</td>\n",
       "      <td>2019-04-02T03:43:28.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>CF946210847851</td>\n",
       "      <td>2019-04-02 04:29:28</td>\n",
       "      <td>138.969782</td>\n",
       "      <td>40.581485</td>\n",
       "      <td>30.0</td>\n",
       "      <td>5000.0</td>\n",
       "      <td>OIEQNT</td>\n",
       "      <td>R5480015614</td>\n",
       "      <td>2019-04-02 02:42:28</td>\n",
       "      <td>CNYTN-MXZLO</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-104.305571</td>\n",
       "      <td>19.085961</td>\n",
       "      <td>2019-04-02T04:29:28.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>CF946210847851</td>\n",
       "      <td>2019-04-02 04:41:28</td>\n",
       "      <td>139.023647</td>\n",
       "      <td>40.617033</td>\n",
       "      <td>30.0</td>\n",
       "      <td>4900.0</td>\n",
       "      <td>OIEQNT</td>\n",
       "      <td>R5480015614</td>\n",
       "      <td>2019-04-02 02:42:28</td>\n",
       "      <td>CNYTN-MXZLO</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-104.305571</td>\n",
       "      <td>19.085961</td>\n",
       "      <td>2019-04-02T04:41:28.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>CF946210847851</td>\n",
       "      <td>2019-04-02 04:49:28</td>\n",
       "      <td>139.059750</td>\n",
       "      <td>40.641672</td>\n",
       "      <td>30.0</td>\n",
       "      <td>4700.0</td>\n",
       "      <td>OIEQNT</td>\n",
       "      <td>R5480015614</td>\n",
       "      <td>2019-04-02 02:42:28</td>\n",
       "      <td>CNYTN-MXZLO</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-104.305571</td>\n",
       "      <td>19.085961</td>\n",
       "      <td>2019-04-02T04:49:28.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>CF946210847851</td>\n",
       "      <td>2019-04-02 04:53:28</td>\n",
       "      <td>139.077772</td>\n",
       "      <td>40.654002</td>\n",
       "      <td>30.0</td>\n",
       "      <td>4800.0</td>\n",
       "      <td>OIEQNT</td>\n",
       "      <td>R5480015614</td>\n",
       "      <td>2019-04-02 02:42:28</td>\n",
       "      <td>CNYTN-MXZLO</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-104.305571</td>\n",
       "      <td>19.085961</td>\n",
       "      <td>2019-04-02T04:53:28.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>CF946210847851</td>\n",
       "      <td>2019-04-02 05:13:28</td>\n",
       "      <td>139.171020</td>\n",
       "      <td>40.710698</td>\n",
       "      <td>30.0</td>\n",
       "      <td>5500.0</td>\n",
       "      <td>OIEQNT</td>\n",
       "      <td>R5480015614</td>\n",
       "      <td>2019-04-02 02:42:28</td>\n",
       "      <td>CNYTN-MXZLO</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-104.305571</td>\n",
       "      <td>19.085961</td>\n",
       "      <td>2019-04-02T05:13:28.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>CF946210847851</td>\n",
       "      <td>2019-04-02 05:17:28</td>\n",
       "      <td>139.190528</td>\n",
       "      <td>40.720732</td>\n",
       "      <td>29.0</td>\n",
       "      <td>5500.0</td>\n",
       "      <td>OIEQNT</td>\n",
       "      <td>R5480015614</td>\n",
       "      <td>2019-04-02 02:42:28</td>\n",
       "      <td>CNYTN-MXZLO</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-104.305571</td>\n",
       "      <td>19.085961</td>\n",
       "      <td>2019-04-02T05:17:28.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>CF946210847851</td>\n",
       "      <td>2019-04-02 05:37:28</td>\n",
       "      <td>139.287605</td>\n",
       "      <td>40.770420</td>\n",
       "      <td>30.0</td>\n",
       "      <td>5700.0</td>\n",
       "      <td>OIEQNT</td>\n",
       "      <td>R5480015614</td>\n",
       "      <td>2019-04-02 02:42:28</td>\n",
       "      <td>CNYTN-MXZLO</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-104.305571</td>\n",
       "      <td>19.085961</td>\n",
       "      <td>2019-04-02T05:37:28.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>CF946210847851</td>\n",
       "      <td>2019-04-02 05:41:28</td>\n",
       "      <td>139.307427</td>\n",
       "      <td>40.780312</td>\n",
       "      <td>29.0</td>\n",
       "      <td>5600.0</td>\n",
       "      <td>OIEQNT</td>\n",
       "      <td>R5480015614</td>\n",
       "      <td>2019-04-02 02:42:28</td>\n",
       "      <td>CNYTN-MXZLO</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-104.305571</td>\n",
       "      <td>19.085961</td>\n",
       "      <td>2019-04-02T05:41:28.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>CF946210847851</td>\n",
       "      <td>2019-04-02 06:41:28</td>\n",
       "      <td>139.587912</td>\n",
       "      <td>40.928193</td>\n",
       "      <td>28.0</td>\n",
       "      <td>5000.0</td>\n",
       "      <td>OIEQNT</td>\n",
       "      <td>R5480015614</td>\n",
       "      <td>2019-04-02 02:42:28</td>\n",
       "      <td>CNYTN-MXZLO</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-104.305571</td>\n",
       "      <td>19.085961</td>\n",
       "      <td>2019-04-02T06:41:28.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>CF946210847851</td>\n",
       "      <td>2019-04-02 06:49:28</td>\n",
       "      <td>139.623400</td>\n",
       "      <td>40.949688</td>\n",
       "      <td>28.0</td>\n",
       "      <td>5100.0</td>\n",
       "      <td>OIEQNT</td>\n",
       "      <td>R5480015614</td>\n",
       "      <td>2019-04-02 02:42:28</td>\n",
       "      <td>CNYTN-MXZLO</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-104.305571</td>\n",
       "      <td>19.085961</td>\n",
       "      <td>2019-04-02T06:49:28.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>CF946210847851</td>\n",
       "      <td>2019-04-02 07:09:28</td>\n",
       "      <td>139.714593</td>\n",
       "      <td>41.006438</td>\n",
       "      <td>32.0</td>\n",
       "      <td>5100.0</td>\n",
       "      <td>OIEQNT</td>\n",
       "      <td>R5480015614</td>\n",
       "      <td>2019-04-02 02:42:28</td>\n",
       "      <td>CNYTN-MXZLO</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-104.305571</td>\n",
       "      <td>19.085961</td>\n",
       "      <td>2019-04-02T07:09:28.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>CF946210847851</td>\n",
       "      <td>2019-04-02 07:21:28</td>\n",
       "      <td>139.771130</td>\n",
       "      <td>41.042028</td>\n",
       "      <td>30.0</td>\n",
       "      <td>5000.0</td>\n",
       "      <td>OIEQNT</td>\n",
       "      <td>R5480015614</td>\n",
       "      <td>2019-04-02 02:42:28</td>\n",
       "      <td>CNYTN-MXZLO</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-104.305571</td>\n",
       "      <td>19.085961</td>\n",
       "      <td>2019-04-02T07:21:28.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>CF946210847851</td>\n",
       "      <td>2019-04-02 07:25:28</td>\n",
       "      <td>139.790210</td>\n",
       "      <td>41.053865</td>\n",
       "      <td>31.0</td>\n",
       "      <td>5000.0</td>\n",
       "      <td>OIEQNT</td>\n",
       "      <td>R5480015614</td>\n",
       "      <td>2019-04-02 02:42:28</td>\n",
       "      <td>CNYTN-MXZLO</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-104.305571</td>\n",
       "      <td>19.085961</td>\n",
       "      <td>2019-04-02T07:25:28.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>CF946210847851</td>\n",
       "      <td>2019-04-02 07:41:28</td>\n",
       "      <td>139.867043</td>\n",
       "      <td>41.100788</td>\n",
       "      <td>30.0</td>\n",
       "      <td>5100.0</td>\n",
       "      <td>OIEQNT</td>\n",
       "      <td>R5480015614</td>\n",
       "      <td>2019-04-02 02:42:28</td>\n",
       "      <td>CNYTN-MXZLO</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-104.305571</td>\n",
       "      <td>19.085961</td>\n",
       "      <td>2019-04-02T07:41:28.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>CF946210847851</td>\n",
       "      <td>2019-04-02 08:13:28</td>\n",
       "      <td>140.020907</td>\n",
       "      <td>41.190743</td>\n",
       "      <td>30.0</td>\n",
       "      <td>5400.0</td>\n",
       "      <td>OIEQNT</td>\n",
       "      <td>R5480015614</td>\n",
       "      <td>2019-04-02 02:42:28</td>\n",
       "      <td>CNYTN-MXZLO</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-104.305571</td>\n",
       "      <td>19.085961</td>\n",
       "      <td>2019-04-02T08:13:28.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>CF946210847851</td>\n",
       "      <td>2019-04-02 08:17:28</td>\n",
       "      <td>140.041278</td>\n",
       "      <td>41.201677</td>\n",
       "      <td>30.0</td>\n",
       "      <td>5500.0</td>\n",
       "      <td>OIEQNT</td>\n",
       "      <td>R5480015614</td>\n",
       "      <td>2019-04-02 02:42:28</td>\n",
       "      <td>CNYTN-MXZLO</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-104.305571</td>\n",
       "      <td>19.085961</td>\n",
       "      <td>2019-04-02T08:17:28.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>CF946210847851</td>\n",
       "      <td>2019-04-02 08:37:28</td>\n",
       "      <td>140.144815</td>\n",
       "      <td>41.254007</td>\n",
       "      <td>29.0</td>\n",
       "      <td>6100.0</td>\n",
       "      <td>OIEQNT</td>\n",
       "      <td>R5480015614</td>\n",
       "      <td>2019-04-02 02:42:28</td>\n",
       "      <td>CNYTN-MXZLO</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-104.305571</td>\n",
       "      <td>19.085961</td>\n",
       "      <td>2019-04-02T08:37:28.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>CF946210847851</td>\n",
       "      <td>2019-04-02 08:57:28</td>\n",
       "      <td>140.259217</td>\n",
       "      <td>41.302792</td>\n",
       "      <td>30.0</td>\n",
       "      <td>6000.0</td>\n",
       "      <td>OIEQNT</td>\n",
       "      <td>R5480015614</td>\n",
       "      <td>2019-04-02 02:42:28</td>\n",
       "      <td>CNYTN-MXZLO</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-104.305571</td>\n",
       "      <td>19.085961</td>\n",
       "      <td>2019-04-02T08:57:28.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>CF946210847851</td>\n",
       "      <td>2019-04-02 09:09:28</td>\n",
       "      <td>140.332148</td>\n",
       "      <td>41.332000</td>\n",
       "      <td>33.0</td>\n",
       "      <td>6000.0</td>\n",
       "      <td>OIEQNT</td>\n",
       "      <td>R5480015614</td>\n",
       "      <td>2019-04-02 02:42:28</td>\n",
       "      <td>CNYTN-MXZLO</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-104.305571</td>\n",
       "      <td>19.085961</td>\n",
       "      <td>2019-04-02T09:09:28.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>CF946210847851</td>\n",
       "      <td>2019-04-02 09:17:28</td>\n",
       "      <td>140.379540</td>\n",
       "      <td>41.351527</td>\n",
       "      <td>30.0</td>\n",
       "      <td>6100.0</td>\n",
       "      <td>OIEQNT</td>\n",
       "      <td>R5480015614</td>\n",
       "      <td>2019-04-02 02:42:28</td>\n",
       "      <td>CNYTN-MXZLO</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-104.305571</td>\n",
       "      <td>19.085961</td>\n",
       "      <td>2019-04-02T09:17:28.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>CF946210847851</td>\n",
       "      <td>2019-04-02 09:33:28</td>\n",
       "      <td>140.473077</td>\n",
       "      <td>41.392005</td>\n",
       "      <td>31.0</td>\n",
       "      <td>5900.0</td>\n",
       "      <td>OIEQNT</td>\n",
       "      <td>R5480015614</td>\n",
       "      <td>2019-04-02 02:42:28</td>\n",
       "      <td>CNYTN-MXZLO</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-104.305571</td>\n",
       "      <td>19.085961</td>\n",
       "      <td>2019-04-02T09:33:28.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>CF946210847851</td>\n",
       "      <td>2019-04-02 09:53:28</td>\n",
       "      <td>140.583827</td>\n",
       "      <td>41.452618</td>\n",
       "      <td>30.0</td>\n",
       "      <td>5200.0</td>\n",
       "      <td>OIEQNT</td>\n",
       "      <td>R5480015614</td>\n",
       "      <td>2019-04-02 02:42:28</td>\n",
       "      <td>CNYTN-MXZLO</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-104.305571</td>\n",
       "      <td>19.085961</td>\n",
       "      <td>2019-04-02T09:53:28.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>CF946210847851</td>\n",
       "      <td>2019-04-02 09:57:28</td>\n",
       "      <td>140.604982</td>\n",
       "      <td>41.465583</td>\n",
       "      <td>30.0</td>\n",
       "      <td>5200.0</td>\n",
       "      <td>OIEQNT</td>\n",
       "      <td>R5480015614</td>\n",
       "      <td>2019-04-02 02:42:28</td>\n",
       "      <td>CNYTN-MXZLO</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-104.305571</td>\n",
       "      <td>19.085961</td>\n",
       "      <td>2019-04-02T09:57:28.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>CF946210847851</td>\n",
       "      <td>2019-04-02 10:17:28</td>\n",
       "      <td>140.711703</td>\n",
       "      <td>41.528140</td>\n",
       "      <td>31.0</td>\n",
       "      <td>5300.0</td>\n",
       "      <td>OIEQNT</td>\n",
       "      <td>R5480015614</td>\n",
       "      <td>2019-04-02 02:42:28</td>\n",
       "      <td>CNYTN-MXZLO</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-104.305571</td>\n",
       "      <td>19.085961</td>\n",
       "      <td>2019-04-02T10:17:28.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>CF946210847851</td>\n",
       "      <td>2019-04-02 10:21:28</td>\n",
       "      <td>140.733420</td>\n",
       "      <td>41.540605</td>\n",
       "      <td>31.0</td>\n",
       "      <td>5200.0</td>\n",
       "      <td>OIEQNT</td>\n",
       "      <td>R5480015614</td>\n",
       "      <td>2019-04-02 02:42:28</td>\n",
       "      <td>CNYTN-MXZLO</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-104.305571</td>\n",
       "      <td>19.085961</td>\n",
       "      <td>2019-04-02T10:21:28.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>CF946210847851</td>\n",
       "      <td>2019-04-02 10:37:28</td>\n",
       "      <td>140.827532</td>\n",
       "      <td>41.584132</td>\n",
       "      <td>32.0</td>\n",
       "      <td>6000.0</td>\n",
       "      <td>OIEQNT</td>\n",
       "      <td>R5480015614</td>\n",
       "      <td>2019-04-02 02:42:28</td>\n",
       "      <td>CNYTN-MXZLO</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-104.305571</td>\n",
       "      <td>19.085961</td>\n",
       "      <td>2019-04-02T10:37:28.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45426</th>\n",
       "      <td>XG479584941731</td>\n",
       "      <td>2019-01-13 03:25:08</td>\n",
       "      <td>104.681497</td>\n",
       "      <td>1.753298</td>\n",
       "      <td>28.0</td>\n",
       "      <td>20640.0</td>\n",
       "      <td>JCMFTA</td>\n",
       "      <td>U2218600548</td>\n",
       "      <td>2019-01-10 00:27:58</td>\n",
       "      <td>CNYTN-MATNG</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-5.812980</td>\n",
       "      <td>35.788207</td>\n",
       "      <td>2019-01-13T03:25:08.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45427</th>\n",
       "      <td>XG479584941731</td>\n",
       "      <td>2019-01-13 03:30:08</td>\n",
       "      <td>104.671802</td>\n",
       "      <td>1.734117</td>\n",
       "      <td>0.0</td>\n",
       "      <td>20540.0</td>\n",
       "      <td>JCMFTA</td>\n",
       "      <td>U2218600548</td>\n",
       "      <td>2019-01-10 00:27:58</td>\n",
       "      <td>CNYTN-MATNG</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-5.812980</td>\n",
       "      <td>35.788207</td>\n",
       "      <td>2019-01-13T03:30:08.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45428</th>\n",
       "      <td>XG479584941731</td>\n",
       "      <td>2019-01-13 03:31:08</td>\n",
       "      <td>104.669990</td>\n",
       "      <td>1.730235</td>\n",
       "      <td>0.0</td>\n",
       "      <td>20440.0</td>\n",
       "      <td>JCMFTA</td>\n",
       "      <td>U2218600548</td>\n",
       "      <td>2019-01-10 00:27:58</td>\n",
       "      <td>CNYTN-MATNG</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-5.812980</td>\n",
       "      <td>35.788207</td>\n",
       "      <td>2019-01-13T03:31:08.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45429</th>\n",
       "      <td>XG479584941731</td>\n",
       "      <td>2019-01-13 03:31:38</td>\n",
       "      <td>104.669125</td>\n",
       "      <td>1.728282</td>\n",
       "      <td>0.0</td>\n",
       "      <td>20400.0</td>\n",
       "      <td>JCMFTA</td>\n",
       "      <td>U2218600548</td>\n",
       "      <td>2019-01-10 00:27:58</td>\n",
       "      <td>CNYTN-MATNG</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-5.812980</td>\n",
       "      <td>35.788207</td>\n",
       "      <td>2019-01-13T03:31:38.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45430</th>\n",
       "      <td>XG479584941731</td>\n",
       "      <td>2019-01-13 03:32:08</td>\n",
       "      <td>104.668303</td>\n",
       "      <td>1.726373</td>\n",
       "      <td>0.0</td>\n",
       "      <td>20380.0</td>\n",
       "      <td>JCMFTA</td>\n",
       "      <td>U2218600548</td>\n",
       "      <td>2019-01-10 00:27:58</td>\n",
       "      <td>CNYTN-MATNG</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-5.812980</td>\n",
       "      <td>35.788207</td>\n",
       "      <td>2019-01-13T03:32:08.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45431</th>\n",
       "      <td>XG479584941731</td>\n",
       "      <td>2019-01-13 03:35:08</td>\n",
       "      <td>104.663100</td>\n",
       "      <td>1.714783</td>\n",
       "      <td>0.0</td>\n",
       "      <td>20420.0</td>\n",
       "      <td>JCMFTA</td>\n",
       "      <td>U2218600548</td>\n",
       "      <td>2019-01-10 00:27:58</td>\n",
       "      <td>CNYTN-MATNG</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-5.812980</td>\n",
       "      <td>35.788207</td>\n",
       "      <td>2019-01-13T03:35:08.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45432</th>\n",
       "      <td>XG479584941731</td>\n",
       "      <td>2019-01-13 03:36:08</td>\n",
       "      <td>104.661393</td>\n",
       "      <td>1.710835</td>\n",
       "      <td>0.0</td>\n",
       "      <td>20280.0</td>\n",
       "      <td>JCMFTA</td>\n",
       "      <td>U2218600548</td>\n",
       "      <td>2019-01-10 00:27:58</td>\n",
       "      <td>CNYTN-MATNG</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-5.812980</td>\n",
       "      <td>35.788207</td>\n",
       "      <td>2019-01-13T03:36:08.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45433</th>\n",
       "      <td>XG479584941731</td>\n",
       "      <td>2019-01-13 03:36:38</td>\n",
       "      <td>104.660592</td>\n",
       "      <td>1.708910</td>\n",
       "      <td>0.0</td>\n",
       "      <td>20240.0</td>\n",
       "      <td>JCMFTA</td>\n",
       "      <td>U2218600548</td>\n",
       "      <td>2019-01-10 00:27:58</td>\n",
       "      <td>CNYTN-MATNG</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-5.812980</td>\n",
       "      <td>35.788207</td>\n",
       "      <td>2019-01-13T03:36:38.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45434</th>\n",
       "      <td>XG479584941731</td>\n",
       "      <td>2019-01-13 03:37:08</td>\n",
       "      <td>104.659828</td>\n",
       "      <td>1.706922</td>\n",
       "      <td>0.0</td>\n",
       "      <td>20180.0</td>\n",
       "      <td>JCMFTA</td>\n",
       "      <td>U2218600548</td>\n",
       "      <td>2019-01-10 00:27:58</td>\n",
       "      <td>CNYTN-MATNG</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-5.812980</td>\n",
       "      <td>35.788207</td>\n",
       "      <td>2019-01-13T03:37:08.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45435</th>\n",
       "      <td>XG479584941731</td>\n",
       "      <td>2019-01-13 03:38:08</td>\n",
       "      <td>104.658382</td>\n",
       "      <td>1.702923</td>\n",
       "      <td>0.0</td>\n",
       "      <td>20000.0</td>\n",
       "      <td>JCMFTA</td>\n",
       "      <td>U2218600548</td>\n",
       "      <td>2019-01-10 00:27:58</td>\n",
       "      <td>CNYTN-MATNG</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-5.812980</td>\n",
       "      <td>35.788207</td>\n",
       "      <td>2019-01-13T03:38:08.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45436</th>\n",
       "      <td>XG479584941731</td>\n",
       "      <td>2019-01-13 03:38:38</td>\n",
       "      <td>104.657677</td>\n",
       "      <td>1.700957</td>\n",
       "      <td>0.0</td>\n",
       "      <td>19950.0</td>\n",
       "      <td>JCMFTA</td>\n",
       "      <td>U2218600548</td>\n",
       "      <td>2019-01-10 00:27:58</td>\n",
       "      <td>CNYTN-MATNG</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-5.812980</td>\n",
       "      <td>35.788207</td>\n",
       "      <td>2019-01-13T03:38:38.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45437</th>\n",
       "      <td>XG479584941731</td>\n",
       "      <td>2019-01-13 03:39:08</td>\n",
       "      <td>104.656957</td>\n",
       "      <td>1.698965</td>\n",
       "      <td>0.0</td>\n",
       "      <td>19970.0</td>\n",
       "      <td>JCMFTA</td>\n",
       "      <td>U2218600548</td>\n",
       "      <td>2019-01-10 00:27:58</td>\n",
       "      <td>CNYTN-MATNG</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-5.812980</td>\n",
       "      <td>35.788207</td>\n",
       "      <td>2019-01-13T03:39:08.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45438</th>\n",
       "      <td>XG479584941731</td>\n",
       "      <td>2019-01-13 03:39:38</td>\n",
       "      <td>104.656230</td>\n",
       "      <td>1.696972</td>\n",
       "      <td>0.0</td>\n",
       "      <td>20000.0</td>\n",
       "      <td>JCMFTA</td>\n",
       "      <td>U2218600548</td>\n",
       "      <td>2019-01-10 00:27:58</td>\n",
       "      <td>CNYTN-MATNG</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-5.812980</td>\n",
       "      <td>35.788207</td>\n",
       "      <td>2019-01-13T03:39:38.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45439</th>\n",
       "      <td>XG479584941731</td>\n",
       "      <td>2019-01-13 03:40:08</td>\n",
       "      <td>104.655497</td>\n",
       "      <td>1.694992</td>\n",
       "      <td>0.0</td>\n",
       "      <td>20010.0</td>\n",
       "      <td>JCMFTA</td>\n",
       "      <td>U2218600548</td>\n",
       "      <td>2019-01-10 00:27:58</td>\n",
       "      <td>CNYTN-MATNG</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-5.812980</td>\n",
       "      <td>35.788207</td>\n",
       "      <td>2019-01-13T03:40:08.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45440</th>\n",
       "      <td>XG479584941731</td>\n",
       "      <td>2019-01-13 03:41:38</td>\n",
       "      <td>104.653290</td>\n",
       "      <td>1.689055</td>\n",
       "      <td>0.0</td>\n",
       "      <td>20070.0</td>\n",
       "      <td>JCMFTA</td>\n",
       "      <td>U2218600548</td>\n",
       "      <td>2019-01-10 00:27:58</td>\n",
       "      <td>CNYTN-MATNG</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-5.812980</td>\n",
       "      <td>35.788207</td>\n",
       "      <td>2019-01-13T03:41:38.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45441</th>\n",
       "      <td>XG479584941731</td>\n",
       "      <td>2019-01-13 03:48:08</td>\n",
       "      <td>104.644218</td>\n",
       "      <td>1.663000</td>\n",
       "      <td>0.0</td>\n",
       "      <td>19830.0</td>\n",
       "      <td>JCMFTA</td>\n",
       "      <td>U2218600548</td>\n",
       "      <td>2019-01-10 00:27:58</td>\n",
       "      <td>CNYTN-MATNG</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-5.812980</td>\n",
       "      <td>35.788207</td>\n",
       "      <td>2019-01-13T03:48:08.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45442</th>\n",
       "      <td>XG479584941731</td>\n",
       "      <td>2019-01-13 03:49:38</td>\n",
       "      <td>104.642198</td>\n",
       "      <td>1.656933</td>\n",
       "      <td>0.0</td>\n",
       "      <td>19850.0</td>\n",
       "      <td>JCMFTA</td>\n",
       "      <td>U2218600548</td>\n",
       "      <td>2019-01-10 00:27:58</td>\n",
       "      <td>CNYTN-MATNG</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-5.812980</td>\n",
       "      <td>35.788207</td>\n",
       "      <td>2019-01-13T03:49:38.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45443</th>\n",
       "      <td>XG479584941731</td>\n",
       "      <td>2019-01-13 03:50:08</td>\n",
       "      <td>104.641540</td>\n",
       "      <td>1.655013</td>\n",
       "      <td>0.0</td>\n",
       "      <td>19860.0</td>\n",
       "      <td>JCMFTA</td>\n",
       "      <td>U2218600548</td>\n",
       "      <td>2019-01-10 00:27:58</td>\n",
       "      <td>CNYTN-MATNG</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-5.812980</td>\n",
       "      <td>35.788207</td>\n",
       "      <td>2019-01-13T03:50:08.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45444</th>\n",
       "      <td>XG479584941731</td>\n",
       "      <td>2019-01-13 03:50:38</td>\n",
       "      <td>104.640840</td>\n",
       "      <td>1.652885</td>\n",
       "      <td>0.0</td>\n",
       "      <td>19870.0</td>\n",
       "      <td>JCMFTA</td>\n",
       "      <td>U2218600548</td>\n",
       "      <td>2019-01-10 00:27:58</td>\n",
       "      <td>CNYTN-MATNG</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-5.812980</td>\n",
       "      <td>35.788207</td>\n",
       "      <td>2019-01-13T03:50:38.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45445</th>\n",
       "      <td>XG479584941731</td>\n",
       "      <td>2019-01-13 03:52:08</td>\n",
       "      <td>104.638820</td>\n",
       "      <td>1.646903</td>\n",
       "      <td>0.0</td>\n",
       "      <td>19880.0</td>\n",
       "      <td>JCMFTA</td>\n",
       "      <td>U2218600548</td>\n",
       "      <td>2019-01-10 00:27:58</td>\n",
       "      <td>CNYTN-MATNG</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-5.812980</td>\n",
       "      <td>35.788207</td>\n",
       "      <td>2019-01-13T03:52:08.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45446</th>\n",
       "      <td>XG479584941731</td>\n",
       "      <td>2019-01-13 03:52:38</td>\n",
       "      <td>104.638110</td>\n",
       "      <td>1.644825</td>\n",
       "      <td>0.0</td>\n",
       "      <td>19890.0</td>\n",
       "      <td>JCMFTA</td>\n",
       "      <td>U2218600548</td>\n",
       "      <td>2019-01-10 00:27:58</td>\n",
       "      <td>CNYTN-MATNG</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-5.812980</td>\n",
       "      <td>35.788207</td>\n",
       "      <td>2019-01-13T03:52:38.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45447</th>\n",
       "      <td>XG479584941731</td>\n",
       "      <td>2019-01-13 03:53:08</td>\n",
       "      <td>104.637438</td>\n",
       "      <td>1.642830</td>\n",
       "      <td>0.0</td>\n",
       "      <td>19880.0</td>\n",
       "      <td>JCMFTA</td>\n",
       "      <td>U2218600548</td>\n",
       "      <td>2019-01-10 00:27:58</td>\n",
       "      <td>CNYTN-MATNG</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-5.812980</td>\n",
       "      <td>35.788207</td>\n",
       "      <td>2019-01-13T03:53:08.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45448</th>\n",
       "      <td>XG479584941731</td>\n",
       "      <td>2019-01-13 03:53:38</td>\n",
       "      <td>104.636745</td>\n",
       "      <td>1.640783</td>\n",
       "      <td>0.0</td>\n",
       "      <td>19870.0</td>\n",
       "      <td>JCMFTA</td>\n",
       "      <td>U2218600548</td>\n",
       "      <td>2019-01-10 00:27:58</td>\n",
       "      <td>CNYTN-MATNG</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-5.812980</td>\n",
       "      <td>35.788207</td>\n",
       "      <td>2019-01-13T03:53:38.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45449</th>\n",
       "      <td>XG479584941731</td>\n",
       "      <td>2019-01-13 03:54:08</td>\n",
       "      <td>104.636077</td>\n",
       "      <td>1.638745</td>\n",
       "      <td>0.0</td>\n",
       "      <td>19870.0</td>\n",
       "      <td>JCMFTA</td>\n",
       "      <td>U2218600548</td>\n",
       "      <td>2019-01-10 00:27:58</td>\n",
       "      <td>CNYTN-MATNG</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-5.812980</td>\n",
       "      <td>35.788207</td>\n",
       "      <td>2019-01-13T03:54:08.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45450</th>\n",
       "      <td>XG479584941731</td>\n",
       "      <td>2019-01-13 03:55:38</td>\n",
       "      <td>104.634008</td>\n",
       "      <td>1.632692</td>\n",
       "      <td>0.0</td>\n",
       "      <td>19880.0</td>\n",
       "      <td>JCMFTA</td>\n",
       "      <td>U2218600548</td>\n",
       "      <td>2019-01-10 00:27:58</td>\n",
       "      <td>CNYTN-MATNG</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-5.812980</td>\n",
       "      <td>35.788207</td>\n",
       "      <td>2019-01-13T03:55:38.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45451</th>\n",
       "      <td>XG479584941731</td>\n",
       "      <td>2019-01-13 03:56:08</td>\n",
       "      <td>104.633357</td>\n",
       "      <td>1.630708</td>\n",
       "      <td>0.0</td>\n",
       "      <td>19890.0</td>\n",
       "      <td>JCMFTA</td>\n",
       "      <td>U2218600548</td>\n",
       "      <td>2019-01-10 00:27:58</td>\n",
       "      <td>CNYTN-MATNG</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-5.812980</td>\n",
       "      <td>35.788207</td>\n",
       "      <td>2019-01-13T03:56:08.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45452</th>\n",
       "      <td>XG479584941731</td>\n",
       "      <td>2019-01-13 03:57:08</td>\n",
       "      <td>104.631958</td>\n",
       "      <td>1.626713</td>\n",
       "      <td>0.0</td>\n",
       "      <td>19890.0</td>\n",
       "      <td>JCMFTA</td>\n",
       "      <td>U2218600548</td>\n",
       "      <td>2019-01-10 00:27:58</td>\n",
       "      <td>CNYTN-MATNG</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-5.812980</td>\n",
       "      <td>35.788207</td>\n",
       "      <td>2019-01-13T03:57:08.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45453</th>\n",
       "      <td>XG479584941731</td>\n",
       "      <td>2019-01-13 03:57:38</td>\n",
       "      <td>104.631258</td>\n",
       "      <td>1.624615</td>\n",
       "      <td>0.0</td>\n",
       "      <td>19900.0</td>\n",
       "      <td>JCMFTA</td>\n",
       "      <td>U2218600548</td>\n",
       "      <td>2019-01-10 00:27:58</td>\n",
       "      <td>CNYTN-MATNG</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-5.812980</td>\n",
       "      <td>35.788207</td>\n",
       "      <td>2019-01-13T03:57:38.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45454</th>\n",
       "      <td>XG479584941731</td>\n",
       "      <td>2019-01-13 03:58:08</td>\n",
       "      <td>104.630597</td>\n",
       "      <td>1.622682</td>\n",
       "      <td>0.0</td>\n",
       "      <td>19910.0</td>\n",
       "      <td>JCMFTA</td>\n",
       "      <td>U2218600548</td>\n",
       "      <td>2019-01-10 00:27:58</td>\n",
       "      <td>CNYTN-MATNG</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-5.812980</td>\n",
       "      <td>35.788207</td>\n",
       "      <td>2019-01-13T03:58:08.000Z</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45455</th>\n",
       "      <td>XG479584941731</td>\n",
       "      <td>2019-01-13 03:59:08</td>\n",
       "      <td>104.629178</td>\n",
       "      <td>1.618552</td>\n",
       "      <td>0.0</td>\n",
       "      <td>19930.0</td>\n",
       "      <td>JCMFTA</td>\n",
       "      <td>U2218600548</td>\n",
       "      <td>2019-01-10 00:27:58</td>\n",
       "      <td>CNYTN-MATNG</td>\n",
       "      <td>114.275347</td>\n",
       "      <td>22.5777</td>\n",
       "      <td>-5.812980</td>\n",
       "      <td>35.788207</td>\n",
       "      <td>2019-01-13T03:59:08.000Z</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>45456 rows × 15 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         loadingOrder           timestamp   longitude   latitude  speed  \\\n",
       "0      CF946210847851 2019-04-02 02:42:28  138.471062  40.278787   31.0   \n",
       "1      CF946210847851 2019-04-02 02:59:28  138.552168  40.327785   30.0   \n",
       "2      CF946210847851 2019-04-02 03:07:28  138.588250  40.352542   30.0   \n",
       "3      CF946210847851 2019-04-02 03:43:28  138.751325  40.459447   30.0   \n",
       "4      CF946210847851 2019-04-02 04:29:28  138.969782  40.581485   30.0   \n",
       "5      CF946210847851 2019-04-02 04:41:28  139.023647  40.617033   30.0   \n",
       "6      CF946210847851 2019-04-02 04:49:28  139.059750  40.641672   30.0   \n",
       "7      CF946210847851 2019-04-02 04:53:28  139.077772  40.654002   30.0   \n",
       "8      CF946210847851 2019-04-02 05:13:28  139.171020  40.710698   30.0   \n",
       "9      CF946210847851 2019-04-02 05:17:28  139.190528  40.720732   29.0   \n",
       "10     CF946210847851 2019-04-02 05:37:28  139.287605  40.770420   30.0   \n",
       "11     CF946210847851 2019-04-02 05:41:28  139.307427  40.780312   29.0   \n",
       "12     CF946210847851 2019-04-02 06:41:28  139.587912  40.928193   28.0   \n",
       "13     CF946210847851 2019-04-02 06:49:28  139.623400  40.949688   28.0   \n",
       "14     CF946210847851 2019-04-02 07:09:28  139.714593  41.006438   32.0   \n",
       "15     CF946210847851 2019-04-02 07:21:28  139.771130  41.042028   30.0   \n",
       "16     CF946210847851 2019-04-02 07:25:28  139.790210  41.053865   31.0   \n",
       "17     CF946210847851 2019-04-02 07:41:28  139.867043  41.100788   30.0   \n",
       "18     CF946210847851 2019-04-02 08:13:28  140.020907  41.190743   30.0   \n",
       "19     CF946210847851 2019-04-02 08:17:28  140.041278  41.201677   30.0   \n",
       "20     CF946210847851 2019-04-02 08:37:28  140.144815  41.254007   29.0   \n",
       "21     CF946210847851 2019-04-02 08:57:28  140.259217  41.302792   30.0   \n",
       "22     CF946210847851 2019-04-02 09:09:28  140.332148  41.332000   33.0   \n",
       "23     CF946210847851 2019-04-02 09:17:28  140.379540  41.351527   30.0   \n",
       "24     CF946210847851 2019-04-02 09:33:28  140.473077  41.392005   31.0   \n",
       "25     CF946210847851 2019-04-02 09:53:28  140.583827  41.452618   30.0   \n",
       "26     CF946210847851 2019-04-02 09:57:28  140.604982  41.465583   30.0   \n",
       "27     CF946210847851 2019-04-02 10:17:28  140.711703  41.528140   31.0   \n",
       "28     CF946210847851 2019-04-02 10:21:28  140.733420  41.540605   31.0   \n",
       "29     CF946210847851 2019-04-02 10:37:28  140.827532  41.584132   32.0   \n",
       "...               ...                 ...         ...        ...    ...   \n",
       "45426  XG479584941731 2019-01-13 03:25:08  104.681497   1.753298   28.0   \n",
       "45427  XG479584941731 2019-01-13 03:30:08  104.671802   1.734117    0.0   \n",
       "45428  XG479584941731 2019-01-13 03:31:08  104.669990   1.730235    0.0   \n",
       "45429  XG479584941731 2019-01-13 03:31:38  104.669125   1.728282    0.0   \n",
       "45430  XG479584941731 2019-01-13 03:32:08  104.668303   1.726373    0.0   \n",
       "45431  XG479584941731 2019-01-13 03:35:08  104.663100   1.714783    0.0   \n",
       "45432  XG479584941731 2019-01-13 03:36:08  104.661393   1.710835    0.0   \n",
       "45433  XG479584941731 2019-01-13 03:36:38  104.660592   1.708910    0.0   \n",
       "45434  XG479584941731 2019-01-13 03:37:08  104.659828   1.706922    0.0   \n",
       "45435  XG479584941731 2019-01-13 03:38:08  104.658382   1.702923    0.0   \n",
       "45436  XG479584941731 2019-01-13 03:38:38  104.657677   1.700957    0.0   \n",
       "45437  XG479584941731 2019-01-13 03:39:08  104.656957   1.698965    0.0   \n",
       "45438  XG479584941731 2019-01-13 03:39:38  104.656230   1.696972    0.0   \n",
       "45439  XG479584941731 2019-01-13 03:40:08  104.655497   1.694992    0.0   \n",
       "45440  XG479584941731 2019-01-13 03:41:38  104.653290   1.689055    0.0   \n",
       "45441  XG479584941731 2019-01-13 03:48:08  104.644218   1.663000    0.0   \n",
       "45442  XG479584941731 2019-01-13 03:49:38  104.642198   1.656933    0.0   \n",
       "45443  XG479584941731 2019-01-13 03:50:08  104.641540   1.655013    0.0   \n",
       "45444  XG479584941731 2019-01-13 03:50:38  104.640840   1.652885    0.0   \n",
       "45445  XG479584941731 2019-01-13 03:52:08  104.638820   1.646903    0.0   \n",
       "45446  XG479584941731 2019-01-13 03:52:38  104.638110   1.644825    0.0   \n",
       "45447  XG479584941731 2019-01-13 03:53:08  104.637438   1.642830    0.0   \n",
       "45448  XG479584941731 2019-01-13 03:53:38  104.636745   1.640783    0.0   \n",
       "45449  XG479584941731 2019-01-13 03:54:08  104.636077   1.638745    0.0   \n",
       "45450  XG479584941731 2019-01-13 03:55:38  104.634008   1.632692    0.0   \n",
       "45451  XG479584941731 2019-01-13 03:56:08  104.633357   1.630708    0.0   \n",
       "45452  XG479584941731 2019-01-13 03:57:08  104.631958   1.626713    0.0   \n",
       "45453  XG479584941731 2019-01-13 03:57:38  104.631258   1.624615    0.0   \n",
       "45454  XG479584941731 2019-01-13 03:58:08  104.630597   1.622682    0.0   \n",
       "45455  XG479584941731 2019-01-13 03:59:08  104.629178   1.618552    0.0   \n",
       "\n",
       "       direction carrierName   vesselMMSI         onboardDate TRANSPORT_TRACE  \\\n",
       "0         5800.0      OIEQNT  R5480015614 2019-04-02 02:42:28     CNYTN-MXZLO   \n",
       "1         4600.0      OIEQNT  R5480015614 2019-04-02 02:42:28     CNYTN-MXZLO   \n",
       "2         4900.0      OIEQNT  R5480015614 2019-04-02 02:42:28     CNYTN-MXZLO   \n",
       "3         5000.0      OIEQNT  R5480015614 2019-04-02 02:42:28     CNYTN-MXZLO   \n",
       "4         5000.0      OIEQNT  R5480015614 2019-04-02 02:42:28     CNYTN-MXZLO   \n",
       "5         4900.0      OIEQNT  R5480015614 2019-04-02 02:42:28     CNYTN-MXZLO   \n",
       "6         4700.0      OIEQNT  R5480015614 2019-04-02 02:42:28     CNYTN-MXZLO   \n",
       "7         4800.0      OIEQNT  R5480015614 2019-04-02 02:42:28     CNYTN-MXZLO   \n",
       "8         5500.0      OIEQNT  R5480015614 2019-04-02 02:42:28     CNYTN-MXZLO   \n",
       "9         5500.0      OIEQNT  R5480015614 2019-04-02 02:42:28     CNYTN-MXZLO   \n",
       "10        5700.0      OIEQNT  R5480015614 2019-04-02 02:42:28     CNYTN-MXZLO   \n",
       "11        5600.0      OIEQNT  R5480015614 2019-04-02 02:42:28     CNYTN-MXZLO   \n",
       "12        5000.0      OIEQNT  R5480015614 2019-04-02 02:42:28     CNYTN-MXZLO   \n",
       "13        5100.0      OIEQNT  R5480015614 2019-04-02 02:42:28     CNYTN-MXZLO   \n",
       "14        5100.0      OIEQNT  R5480015614 2019-04-02 02:42:28     CNYTN-MXZLO   \n",
       "15        5000.0      OIEQNT  R5480015614 2019-04-02 02:42:28     CNYTN-MXZLO   \n",
       "16        5000.0      OIEQNT  R5480015614 2019-04-02 02:42:28     CNYTN-MXZLO   \n",
       "17        5100.0      OIEQNT  R5480015614 2019-04-02 02:42:28     CNYTN-MXZLO   \n",
       "18        5400.0      OIEQNT  R5480015614 2019-04-02 02:42:28     CNYTN-MXZLO   \n",
       "19        5500.0      OIEQNT  R5480015614 2019-04-02 02:42:28     CNYTN-MXZLO   \n",
       "20        6100.0      OIEQNT  R5480015614 2019-04-02 02:42:28     CNYTN-MXZLO   \n",
       "21        6000.0      OIEQNT  R5480015614 2019-04-02 02:42:28     CNYTN-MXZLO   \n",
       "22        6000.0      OIEQNT  R5480015614 2019-04-02 02:42:28     CNYTN-MXZLO   \n",
       "23        6100.0      OIEQNT  R5480015614 2019-04-02 02:42:28     CNYTN-MXZLO   \n",
       "24        5900.0      OIEQNT  R5480015614 2019-04-02 02:42:28     CNYTN-MXZLO   \n",
       "25        5200.0      OIEQNT  R5480015614 2019-04-02 02:42:28     CNYTN-MXZLO   \n",
       "26        5200.0      OIEQNT  R5480015614 2019-04-02 02:42:28     CNYTN-MXZLO   \n",
       "27        5300.0      OIEQNT  R5480015614 2019-04-02 02:42:28     CNYTN-MXZLO   \n",
       "28        5200.0      OIEQNT  R5480015614 2019-04-02 02:42:28     CNYTN-MXZLO   \n",
       "29        6000.0      OIEQNT  R5480015614 2019-04-02 02:42:28     CNYTN-MXZLO   \n",
       "...          ...         ...          ...                 ...             ...   \n",
       "45426    20640.0      JCMFTA  U2218600548 2019-01-10 00:27:58     CNYTN-MATNG   \n",
       "45427    20540.0      JCMFTA  U2218600548 2019-01-10 00:27:58     CNYTN-MATNG   \n",
       "45428    20440.0      JCMFTA  U2218600548 2019-01-10 00:27:58     CNYTN-MATNG   \n",
       "45429    20400.0      JCMFTA  U2218600548 2019-01-10 00:27:58     CNYTN-MATNG   \n",
       "45430    20380.0      JCMFTA  U2218600548 2019-01-10 00:27:58     CNYTN-MATNG   \n",
       "45431    20420.0      JCMFTA  U2218600548 2019-01-10 00:27:58     CNYTN-MATNG   \n",
       "45432    20280.0      JCMFTA  U2218600548 2019-01-10 00:27:58     CNYTN-MATNG   \n",
       "45433    20240.0      JCMFTA  U2218600548 2019-01-10 00:27:58     CNYTN-MATNG   \n",
       "45434    20180.0      JCMFTA  U2218600548 2019-01-10 00:27:58     CNYTN-MATNG   \n",
       "45435    20000.0      JCMFTA  U2218600548 2019-01-10 00:27:58     CNYTN-MATNG   \n",
       "45436    19950.0      JCMFTA  U2218600548 2019-01-10 00:27:58     CNYTN-MATNG   \n",
       "45437    19970.0      JCMFTA  U2218600548 2019-01-10 00:27:58     CNYTN-MATNG   \n",
       "45438    20000.0      JCMFTA  U2218600548 2019-01-10 00:27:58     CNYTN-MATNG   \n",
       "45439    20010.0      JCMFTA  U2218600548 2019-01-10 00:27:58     CNYTN-MATNG   \n",
       "45440    20070.0      JCMFTA  U2218600548 2019-01-10 00:27:58     CNYTN-MATNG   \n",
       "45441    19830.0      JCMFTA  U2218600548 2019-01-10 00:27:58     CNYTN-MATNG   \n",
       "45442    19850.0      JCMFTA  U2218600548 2019-01-10 00:27:58     CNYTN-MATNG   \n",
       "45443    19860.0      JCMFTA  U2218600548 2019-01-10 00:27:58     CNYTN-MATNG   \n",
       "45444    19870.0      JCMFTA  U2218600548 2019-01-10 00:27:58     CNYTN-MATNG   \n",
       "45445    19880.0      JCMFTA  U2218600548 2019-01-10 00:27:58     CNYTN-MATNG   \n",
       "45446    19890.0      JCMFTA  U2218600548 2019-01-10 00:27:58     CNYTN-MATNG   \n",
       "45447    19880.0      JCMFTA  U2218600548 2019-01-10 00:27:58     CNYTN-MATNG   \n",
       "45448    19870.0      JCMFTA  U2218600548 2019-01-10 00:27:58     CNYTN-MATNG   \n",
       "45449    19870.0      JCMFTA  U2218600548 2019-01-10 00:27:58     CNYTN-MATNG   \n",
       "45450    19880.0      JCMFTA  U2218600548 2019-01-10 00:27:58     CNYTN-MATNG   \n",
       "45451    19890.0      JCMFTA  U2218600548 2019-01-10 00:27:58     CNYTN-MATNG   \n",
       "45452    19890.0      JCMFTA  U2218600548 2019-01-10 00:27:58     CNYTN-MATNG   \n",
       "45453    19900.0      JCMFTA  U2218600548 2019-01-10 00:27:58     CNYTN-MATNG   \n",
       "45454    19910.0      JCMFTA  U2218600548 2019-01-10 00:27:58     CNYTN-MATNG   \n",
       "45455    19930.0      JCMFTA  U2218600548 2019-01-10 00:27:58     CNYTN-MATNG   \n",
       "\n",
       "          start_x  start_y       end_x      end_y            temp_timestamp  \n",
       "0      114.275347  22.5777 -104.305571  19.085961  2019-04-02T02:42:28.000Z  \n",
       "1      114.275347  22.5777 -104.305571  19.085961  2019-04-02T02:59:28.000Z  \n",
       "2      114.275347  22.5777 -104.305571  19.085961  2019-04-02T03:07:28.000Z  \n",
       "3      114.275347  22.5777 -104.305571  19.085961  2019-04-02T03:43:28.000Z  \n",
       "4      114.275347  22.5777 -104.305571  19.085961  2019-04-02T04:29:28.000Z  \n",
       "5      114.275347  22.5777 -104.305571  19.085961  2019-04-02T04:41:28.000Z  \n",
       "6      114.275347  22.5777 -104.305571  19.085961  2019-04-02T04:49:28.000Z  \n",
       "7      114.275347  22.5777 -104.305571  19.085961  2019-04-02T04:53:28.000Z  \n",
       "8      114.275347  22.5777 -104.305571  19.085961  2019-04-02T05:13:28.000Z  \n",
       "9      114.275347  22.5777 -104.305571  19.085961  2019-04-02T05:17:28.000Z  \n",
       "10     114.275347  22.5777 -104.305571  19.085961  2019-04-02T05:37:28.000Z  \n",
       "11     114.275347  22.5777 -104.305571  19.085961  2019-04-02T05:41:28.000Z  \n",
       "12     114.275347  22.5777 -104.305571  19.085961  2019-04-02T06:41:28.000Z  \n",
       "13     114.275347  22.5777 -104.305571  19.085961  2019-04-02T06:49:28.000Z  \n",
       "14     114.275347  22.5777 -104.305571  19.085961  2019-04-02T07:09:28.000Z  \n",
       "15     114.275347  22.5777 -104.305571  19.085961  2019-04-02T07:21:28.000Z  \n",
       "16     114.275347  22.5777 -104.305571  19.085961  2019-04-02T07:25:28.000Z  \n",
       "17     114.275347  22.5777 -104.305571  19.085961  2019-04-02T07:41:28.000Z  \n",
       "18     114.275347  22.5777 -104.305571  19.085961  2019-04-02T08:13:28.000Z  \n",
       "19     114.275347  22.5777 -104.305571  19.085961  2019-04-02T08:17:28.000Z  \n",
       "20     114.275347  22.5777 -104.305571  19.085961  2019-04-02T08:37:28.000Z  \n",
       "21     114.275347  22.5777 -104.305571  19.085961  2019-04-02T08:57:28.000Z  \n",
       "22     114.275347  22.5777 -104.305571  19.085961  2019-04-02T09:09:28.000Z  \n",
       "23     114.275347  22.5777 -104.305571  19.085961  2019-04-02T09:17:28.000Z  \n",
       "24     114.275347  22.5777 -104.305571  19.085961  2019-04-02T09:33:28.000Z  \n",
       "25     114.275347  22.5777 -104.305571  19.085961  2019-04-02T09:53:28.000Z  \n",
       "26     114.275347  22.5777 -104.305571  19.085961  2019-04-02T09:57:28.000Z  \n",
       "27     114.275347  22.5777 -104.305571  19.085961  2019-04-02T10:17:28.000Z  \n",
       "28     114.275347  22.5777 -104.305571  19.085961  2019-04-02T10:21:28.000Z  \n",
       "29     114.275347  22.5777 -104.305571  19.085961  2019-04-02T10:37:28.000Z  \n",
       "...           ...      ...         ...        ...                       ...  \n",
       "45426  114.275347  22.5777   -5.812980  35.788207  2019-01-13T03:25:08.000Z  \n",
       "45427  114.275347  22.5777   -5.812980  35.788207  2019-01-13T03:30:08.000Z  \n",
       "45428  114.275347  22.5777   -5.812980  35.788207  2019-01-13T03:31:08.000Z  \n",
       "45429  114.275347  22.5777   -5.812980  35.788207  2019-01-13T03:31:38.000Z  \n",
       "45430  114.275347  22.5777   -5.812980  35.788207  2019-01-13T03:32:08.000Z  \n",
       "45431  114.275347  22.5777   -5.812980  35.788207  2019-01-13T03:35:08.000Z  \n",
       "45432  114.275347  22.5777   -5.812980  35.788207  2019-01-13T03:36:08.000Z  \n",
       "45433  114.275347  22.5777   -5.812980  35.788207  2019-01-13T03:36:38.000Z  \n",
       "45434  114.275347  22.5777   -5.812980  35.788207  2019-01-13T03:37:08.000Z  \n",
       "45435  114.275347  22.5777   -5.812980  35.788207  2019-01-13T03:38:08.000Z  \n",
       "45436  114.275347  22.5777   -5.812980  35.788207  2019-01-13T03:38:38.000Z  \n",
       "45437  114.275347  22.5777   -5.812980  35.788207  2019-01-13T03:39:08.000Z  \n",
       "45438  114.275347  22.5777   -5.812980  35.788207  2019-01-13T03:39:38.000Z  \n",
       "45439  114.275347  22.5777   -5.812980  35.788207  2019-01-13T03:40:08.000Z  \n",
       "45440  114.275347  22.5777   -5.812980  35.788207  2019-01-13T03:41:38.000Z  \n",
       "45441  114.275347  22.5777   -5.812980  35.788207  2019-01-13T03:48:08.000Z  \n",
       "45442  114.275347  22.5777   -5.812980  35.788207  2019-01-13T03:49:38.000Z  \n",
       "45443  114.275347  22.5777   -5.812980  35.788207  2019-01-13T03:50:08.000Z  \n",
       "45444  114.275347  22.5777   -5.812980  35.788207  2019-01-13T03:50:38.000Z  \n",
       "45445  114.275347  22.5777   -5.812980  35.788207  2019-01-13T03:52:08.000Z  \n",
       "45446  114.275347  22.5777   -5.812980  35.788207  2019-01-13T03:52:38.000Z  \n",
       "45447  114.275347  22.5777   -5.812980  35.788207  2019-01-13T03:53:08.000Z  \n",
       "45448  114.275347  22.5777   -5.812980  35.788207  2019-01-13T03:53:38.000Z  \n",
       "45449  114.275347  22.5777   -5.812980  35.788207  2019-01-13T03:54:08.000Z  \n",
       "45450  114.275347  22.5777   -5.812980  35.788207  2019-01-13T03:55:38.000Z  \n",
       "45451  114.275347  22.5777   -5.812980  35.788207  2019-01-13T03:56:08.000Z  \n",
       "45452  114.275347  22.5777   -5.812980  35.788207  2019-01-13T03:57:08.000Z  \n",
       "45453  114.275347  22.5777   -5.812980  35.788207  2019-01-13T03:57:38.000Z  \n",
       "45454  114.275347  22.5777   -5.812980  35.788207  2019-01-13T03:58:08.000Z  \n",
       "45455  114.275347  22.5777   -5.812980  35.788207  2019-01-13T03:59:08.000Z  \n",
       "\n",
       "[45456 rows x 15 columns]"
      ]
     },
     "execution_count": 108,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#\n",
    "df.sort_values(['loadingOrder', 'timestamp'], inplace=True)\n",
    "# 特征只选择经纬度、速度\\方向\n",
    "df['lat_diff'] = df.groupby('loadingOrder')['latitude'].diff(1)#经度变化\n",
    "df['lon_diff'] = df.groupby('loadingOrder')['longitude'].diff(1)#纬度变化\n",
    "df['speed_diff'] = df.groupby('loadingOrder')['speed'].diff(1)#速度变化量\n",
    "df['diff_minutes'] = df.groupby('loadingOrder')['timestamp'].diff(1).dt.total_seconds() // 60#记录之间的间隔时间(单位:s)\n",
    "#经纬度变化;速度变化，间隔时间小于一定阈值的认定为船没有动\n",
    "df['anchor'] = df.apply(lambda x: 1 if x['lat_diff'] <= 0.03 and x['lon_diff'] <= 0.03\n",
    "                        and x['speed_diff'] <= 0.3 and x['diff_minutes'] <= 10 else 0, axis=1)\n",
    "#对于每一个运单，统计最大时间(mmax),记录条数(count),最小时间(mmin )\n",
    "#同时计算时间间隔，也就是运单抵达\"终点\"需要的时间,作为标签\n",
    "if mode=='train':\n",
    "    group_df = df.groupby('loadingOrder')['timestamp'].agg([('mmax','max'), ('count','count'), ('mmin','min')]).reset_index()\n",
    "    # 读取数据的最大值-最小值，即确认时间间隔为label\n",
    "    group_df['label'] = (group_df['mmax'] - group_df['mmin']).dt.total_seconds()\n",
    "elif mode=='test':\n",
    "    group_df = df.groupby('loadingOrder')['timestamp'].agg([('count','count')]).reset_index()\n",
    "#在所有的记录中，多少记录是停船状态\n",
    "anchor_df = df.groupby('loadingOrder')['anchor'].agg('sum').reset_index()\n",
    "anchor_df.columns = ['loadingOrder', 'anchor_cnt']\n",
    "group_df = group_df.merge(anchor_df, on='loadingOrder', how='left')\n",
    "#merge回原表，增加一列: 'anchor_cnt'，同时算一下停船的次数占总记录的比例\n",
    "group_df['anchor_ratio'] = group_df['anchor_cnt'] / group_df['count']\n",
    "#以经纬,度,速度，方向为基础特征，然后计算最小，最大，均值，终值等统计特性（以运单为单位）\n",
    "agg_function = ['min', 'max', 'mean', 'median']\n",
    "agg_col = ['latitude', 'longitude', 'speed', 'direction']\n",
    "\n",
    "group = df.groupby('loadingOrder')[agg_col].agg(agg_function).reset_index()\n",
    "group.columns = ['loadingOrder'] + ['{}_{}'.format(i, j) for i in agg_col for j in agg_function]\n",
    "group_df = group_df.merge(group, on='loadingOrder', how='left')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "huawei",
   "language": "python",
   "name": "huawei"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
